5 Replies Latest reply on Aug 25, 2017 10:56 AM by Deepak Rai

    Calculated field: Replacing a static number with a field without breaking things

    P R

      Mac 10.3

       

      Take a look at the "BA Revenue" tab.

       

      I have a calculated field called "New Revenue Calculation".

       

      Before it was = [Monthly Hours]*150

       

      I changed it to [Monthly Hours]*[ResourceRevenue]

       

      so far so good.

       

      I have another calculated field: "New Expected Billing $"

       

      Before it was = {INCLUDE MONTH([Date Val]),[Name]:([New Expected Utilization])*150}

       

      I am trying to change it to: {INCLUDE MONTH([Date Val]),[Name]:([New Expected Utilization])*[ResourceRevenue]}

       

      But i get an error about mixing aggregate and non aggregate arguments.

       

      How do I fix this?

       

      Thanks

        • 1. Re: Calculated field: Replacing a static number with a field without breaking things
          John Croft

          Not sure if this is exactly what you want. But the LOD calc (FIXED, INCLUDE, EXCLUDE) require an aggregation after the ':' in the formula. So below it needs to aggregate something in the red circled section. In the previous calc, this was accomplished because the Rev Expected Utilization is already aggregated by way of it's custom calculation. You know this when you see AGG() in front of the field name when you drag it to the Tex shelf. (see second screen shot).  So then multiplying by 150, life if good because its a constant.

           

          But when you try to multiply it by ResourceRevenue - this is not aggregated (see third screen shot). It has SUM() not AGG(). So you have to aggregate the field using SUM, MIN, MAX, ATTR, something on the right side of the ':' in the LOD calc to combine it with the other already aggreated New Expected Utilization. So you actually have two issues to consider. 1) LOD calcs need the aggregation inside the red circle below. 2) cannot add aggregated fields to non-aggregated fields.

           

          hope this helps somewhat.

           

           

           

          • 2. Re: Calculated field: Replacing a static number with a field without breaking things
            Deepak Rai

            Hi PR,

            If your ResourceRevenue is constant then use this:

             

            If it is not Constant then, use This:

             

             

            Just Wrote from Top of my Head, as I don't know your data. Please modify, they would work.

            Thanks

            Deepak

            • 3. Re: Calculated field: Replacing a static number with a field without breaking things
              Simon Runc

              hi PR

               

              So your [New Expected Utilization] field is made up from 2 aggregate calculations (the they both have the aggregate MAX)

               

              [New Expected Utilization]

              [Avail Hours to Work]*[Target Utilization]

               

              [Avail Hours to Work]

              max(IF MONTH([EmpStartDate])=MONTH([Date Val])

              AND YEAR([EmpStartDate])=YEAR([Date Val])

              THEN ((Datediff('day',[EmpStartDate],[Date Val]))+1)*5.16

              ELSE 160

               

              [Target Utilization]

              MAX([Yearly Target Hours])/1920

               

              So you can either add an aggregation in your

               

              [New Expected Utilization]

              {INCLUDE MONTH([Date Val]),[Name]:([New Expected Utilization])*MAX([ResourceRevenue])}

               

              or you can wrap the [Avail Hours to Work] and [Target Utilization] in FIXED LoDs (or indeed the [New Expected Utilization] calculation). Using FIXED LoDs means the field can be calculated as an aggregate (where you can determine the Level of Detail they are aggregated over), but the results are returned at row level (as the Level of Detail specified in the FIXED LoD)

               

              Which option is better will depend on how you want the values calculated.

               

              Hope that makes sense?

              1 of 1 people found this helpful
              • 4. Re: Calculated field: Replacing a static number with a field without breaking things
                P R

                The revenue value is different from person to person. So I should use the 2nd one?