Grouping by Project Actual Calculation but Aggregating Data as well

[MaryMc1]

Hello,

We have a user looking for a project report that groups the data in a chart by project actual duration and we know we need to make a calculated field, put that on a form on the projects to get that information. However, in a bar chart, that pulls in every possibly calculation and we'd like to instead add an expression to our calculation in the calculated field to further break those down by time frames like 0-30 days, 30-60 days, 60-90 days, 90+ days. I think that's an aggregate but not sure and was looking for advice on what I'd add to my calculated field calculation to get that. Here's the calculation below we have so far in our field that is working but again, need to add something to this to get the results in those time frames. I'm adding a screenshot of a test report and how we're trying to get this to group in a bar chart. Thanks in advance!

IF($$OBJCODE="PROJ",ROUND(DIV(WORKMINUTESDIFF({actualStartDate},{actualCompletionDate}),480),2),"")

[Richard_Le_]

Hi,

Theres a couple of different routes you could date here:

Option 1

You update the existing project duration field to include a set of nested if statements that return the appropriate "aggregated value". Note that this code wont work if your field is set to number data type, it needs to be text. Your code would look something like this:

IF($$OBJCODE="PROJ",IF(ROUND(DIV(WORKMINUTESDIFF({actualStartDate},{actualCompletionDate}),480),2)<=30,"30 days",IF(ROUND(DIV(WORKMINUTESDIFF({actualStartDate},{actualCompletionDate}),480),2)<=60,"30-60 days",IF(ROUND(DIV(WORKMINUTESDIFF({actualStartDate},{actualCompletionDate}),480),2)<=90,"60-90 days",IF(ROUND(DIV(WORKMINUTESDIFF({actualStartDate},{actualCompletionDate}),480),2)>90,"90+ days")))),"")

Option 2

You create a new calculated field that references the original project duration field and returns the aggregated value. I would lean towards this option because it means that you retain the actual duration measurement (in the first field) and also have a second field that outputs the aggregated value. The code would look something like this. NOTE that you will need to replace "Field Name" with the name of the project duration field in your instance. 

IF({DE:Field Name}<=30,"30 days",IF({DE:Field Name}<=60,"30-60 days",IF({DE:Field Name}<=90,"60-90 days","90+ days")))

You can then use this field in the grouping of your report to return a bar chart displaying the aggregated values.

Best Regards,

Rich.

[MaryMc1]

Hi Rich,

This is fantastic and SO helpful! I went with your second option and it seems to have worked in several cases but in others, it didn't. For example, attaching screenshots, some of the actual durations would fall in either the 30 or 30-60 range but are going into the 90+? Not sure why that is as your calculation worked and I'll include a screenshot of that referencing our other custom calculated field. Both calculated fields format are text, should they both be number? Thank you SO much for your help!
Mary

[Richard_Le_]

Glad I could help.

Yes, you're likely to find that the problem is that your duration field is set as text and not a number. The IF statement isn't recognising the number values to see if it's <30/60/90 days, so it's always falling into the else condition of 90+ days. 

I'd try again but with the duration field set to number format and you should find that it works. The new "aggregated duration" field can stay as text data format. 

Best Regards,

Rich.

[MaryMc1]

Hi Rich,

Got it and changed my duration field to number and that worked!

Do you have a good "rule of thumb" of when a calc field is text vs. number depending on what you're looking to do? Just trying to make sure I don't make that mistake again in the future and catch that up front. I appreciate your help so much! Very grateful to you! Thanks!
Mary