Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hope you can help me here but I cannot get a count to work where I'm looking to count only the instances that meet a condition..
The challenge is to display as a KPI the number of instances where a % condition is satisfied e.g. (A-B)/A >= 0.9. So count each instance where this condition is true
I'm assuming the solution is count if but I've tried all manner of variations without success ?
Thanks in anticipation
Kevin
assuming you have %Utilization expression as below
(Sum(A)-Sum(B))/sum(A)
then you can use below expression in text object to count
sum(aggr(if((Sum(A)-Sum(B))/sum(A) >=0.9, 1,0),Month,Subscription,all_id))
Maybe try Sum(Aggr(Sum(IF(A-B)/A>=0.9, 1, 0)), Dim1))
Thanks Almen - I'm not getting any results with that. Here's a bit more detail as to the output required for the business.
I'm looking at allowances for mobile phone users and the % usage for each allowance, then highlighting mobile phone users who's usage of allowances is greater than 90%.
I work out the % of the allowance used by taking the allowance value (A) - e.g. 100 minutes, subtracting the remaining allowance value (B) - e.g 40 minutes to give the the allowance used(A-B) in this example that would be 60 minutes.
I then divide the allowance used (A-B) by the allowance value (A) to give the % in this case 0.6.
I only want to count mobile phone users where the % allowance used exceeds a value of 0.9
Hope that helps with the context
Please share some sample data with expected output?
Yeah sorry, I misplaced a bracket. Try this:
Sum(Aggr(Sum(IF(([A]-[B])/A>=0.9, 1, 0)), YourDimension))
A and B are expressions or are they fields from the scripts?
Hi Sunny - they are fields - (Value A -Value B)/Value A
Hi Kush,
I've created this table in my app to show the details where I have calculated the allowance >= 90% figure for each month, each allowance and each subscriber - in a KPI I would just like to display a count of the those instances where I have a value in the table - the count would be 17 in this example.
Another question is how do I just show the values in the table that meet the condition rather than having blank entries?
assuming you have %Utilization expression as below
(Sum(A)-Sum(B))/sum(A)
then you can use below expression in text object to count
sum(aggr(if((Sum(A)-Sum(B))/sum(A) >=0.9, 1,0),Month,Subscription,all_id))
Thanks Kush, that nailed it 👍