Skip to main content

App Development

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

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
drew61199
Contributor
Contributor

COUNT where SUM exceeds a threshold....grouped by months

I have a vehicle utilization dashboard and need to count the vehicles that exceed 5000 mi / month. I'm using the below in a barchart and it works great if I slice to an individual month, but if I have May & June selected, then it counts all those that now exceed 5000 across the two months. 

SUM(AGGR(IF(SUM(Utilization)>5000,1,0),vehicle))

I have a straight table below to view the data and I can see if a vehicle has 2600 for May and 2500 for June, so I know it should 0, but the barchart will show 1. So how do I alter my formula to only count the instances within individual months?

Labels (1)
1 Solution

Accepted Solutions
Or
Champion
Champion

Perhaps something along the lines of:

Count(distinct AGGR(IF(SUM(Utilization)>5000,vehicle),vehicle,month))

 

 

View solution in original post

4 Replies
edwin
Master
Master

you need to aggregate by month and vehicle first

if you have sample data it will be easier to show you

Or
Champion
Champion

Perhaps something along the lines of:

Count(distinct AGGR(IF(SUM(Utilization)>5000,vehicle),vehicle,month))

 

 

edwin
Master
Master

try this

sum(if(aggr(Sum(amount), vehicle, Month)>5000,1,0))

drew61199
Contributor
Contributor
Author

Thank you! I knew it would likely be something simply / silly. I removed the distinct b/c I wanted a tally of the months a vehicle exceeded it, but otherwise worked perfectly