Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Perhaps something along the lines of:
Count(distinct AGGR(IF(SUM(Utilization)>5000,vehicle),vehicle,month))
you need to aggregate by month and vehicle first
if you have sample data it will be easier to show you
Perhaps something along the lines of:
Count(distinct AGGR(IF(SUM(Utilization)>5000,vehicle),vehicle,month))
try this
sum(if(aggr(Sum(amount), vehicle, Month)>5000,1,0))
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