topic COUNT where SUM exceeds a threshold....grouped by months in App Development

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

drew61199 — Tue, 02 Aug 2022 15:51:50 GMT

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?

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

edwin — Tue, 02 Aug 2022 16:27:25 GMT

you need to aggregate by month and vehicle first

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

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

Or — Tue, 02 Aug 2022 16:30:13 GMT

Perhaps something along the lines of:

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

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

edwin — Tue, 02 Aug 2022 16:53:37 GMT

try this

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

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

drew61199 — Tue, 02 Aug 2022 17:19:44 GMT

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