Count Results of an expression

[kevinstanfield]

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

[Almen]

Maybe try Sum(Aggr(Sum(IF(A-B)/A>=0.9, 1, 0)), Dim1))

[kevinstanfield]

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 

[Kushal_Chawda]

Please share some sample data with expected output?

[Almen]

Yeah sorry, I misplaced a bracket. Try this:

Sum(Aggr(Sum(IF(([A]-[B])/A>=0.9, 1, 0)), YourDimension))

[sunny_talwar]

A and B are expressions or are they fields from the scripts?

[kevinstanfield]

Hi Sunny - they are fields - (Value A -Value B)/Value A

[kevinstanfield]

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?

[Kushal_Chawda · Accepted solution]

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))

[kevinstanfield]

Thanks Kush, that nailed it 👍