Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

william_walsh2
New Contributor II

Using aggr() and count

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)

1 Solution

Accepted Solutions
MVP
MVP

Re: Using aggr() and count

Try as calculated dimension

Aggr( if(sum(Actual)/Budget > 1.5, Dual('150% & Greater',1), ProjectName)

edit: replace the if() with whatever you need to classify your aggregated values

1 Reply
MVP
MVP

Re: Using aggr() and count

Try as calculated dimension

Aggr( if(sum(Actual)/Budget > 1.5, Dual('150% & Greater',1), ProjectName)

edit: replace the if() with whatever you need to classify your aggregated values