Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Count Results of an expression

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

1 Solution

Accepted Solutions
MVP
MVP

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

View solution in original post

9 Replies
Creator
Creator

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

Partner
Partner

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 

MVP
MVP

Please share some sample data with expected output?

Creator
Creator

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?

Partner
Partner

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

Partner
Partner

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?

MVP
MVP

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

View solution in original post

Partner
Partner

Thanks Kush, that nailed it 👍