1 Reply Latest reply: Sep 11, 2017 11:19 AM by Stefan Wühl RSS

    Using aggr() and count

    William Walsh

      Hi,

       

      Im having trouble calculating the following dataset due to not be able to certain functions in dimensions such as the sum() function.

      This table is my dataset.

           

      ProjectNameFundingProgramBudgetActual
      Project11A10030
      Project12A10020
      Project21A20030
      Project31B30050
      Project32C300200
      Project33C30010
      Project41A20040
      Project42A20030
      Project52A10050

       

      I want to be able to put the data in a bar chart using the dual() function to calculate the percentage of Actual against the Budget but I cant total the actual column due to being able to use the sum() function in the dimension. Is there an aggr() I could use or other function or load method to sum the Actual value for each specific project.

       

      This is the dimension I want to use but cant. if(sum(Actual)/Budget > 1.5, Dual('150% & Greater',1).


      The following code is where I am currently.

       

      Dimension=if(Actual/Budget > 1.5, Dual('150% & Greater',1),

         

      if(Actual/Budget > 1.25, Dual('125% - 150%',2),

         

      if(Actual/Budget  > .75, Dual('75% - 125%',3),

         

      if(Actual/Budget  >= .50, Dual('50% - 75%',4),

         

      if(Actual/Budget  < .50, Dual('Below 50%',5)            

      )))))

       

      Measure = Count(Distinct ProjectName)