Grouping by Project Actual Calculation but Aggregating Data as well | Adobe Higher Education
Skip to main content
MaryMc1
MaryMc1
Level 3
July 1, 2024
Frage

Grouping by Project Actual Calculation but Aggregating Data as well

  • July 1, 2024
  • 1 Antwort
  • 805 Ansichten

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),"")
Dieses Thema wurde für Antworten geschlossen.

1 Antwort

R
Community Advisor
Richard_Le_Community Advisor
Community Advisor
July 2, 2024

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
    MaryMc1Autor
    Level 3
    July 2, 2024

    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

    R
    Community Advisor
    Richard_Le_Community Advisor
    Community Advisor
    July 2, 2024

    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.

    Allgemeine NutzungsbedingungenAccessibility statement

    Loading footer...