Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
swuehl
MVP
MVP

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

View solution in original post

1 Reply
swuehl
MVP
MVP

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