Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
sayyam005
Contributor
Contributor

Count no.of times one value is greater than other

I want to count no.of times " Max({$<FactKey={4}>}xSale)*-1 " is greater than " Max({<Scenario>}ySale)*100 " using KPI with following formula:

Count(Aggr((Max({$<FactKey={4}>}xSale)*-1) > (Max({<Scenario>}ySale)*100),Country))

but the output is incorrect. What is wrong with this formula. Also i want that my count should not include any value where either of the above measures (Max({$<FactKey={4}>}xSale)*-1  or  Max({<Scenario>}ySale)*100 )are null.

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

You already had it working in your table, right? Try this

Sum(Aggr(If(IsNull (Max(ySale)) OR IsNull( Max(xSale)) OR Max(xSale) <= Max(ySale), 0, 1), BusinessDate, xSale, ySale))

View solution in original post

16 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Count is rather adverse if the result of your comparing is either -1 or 0 for true respecitively false which are both valid numbers and would be counted - NULL by one part would return NULL on the country-level and should not impact the result. Therefore I think the following should work:

-sum(Aggr(Max({$<FactKey={4}>}xSale)*-1 > Max({<Scenario>}ySale)*100,Country))

- Marcus

sunny_talwar

What are you getting? and what is the expected output? Can you share a sample to look at?

sayyam005
Contributor
Contributor
Author

this just gives value 1 It should be 12. Actually i can remove country from aggregation as it will be used in filter.

i just want to count no.of times " Max({$<FactKey={4}>}xSale)*-1 " is greater than " Max({<Scenario>}ySale)*100"

sayyam005
Contributor
Contributor
Author

Simply ,if possible tell me how do i count no. of times  xsale > ysale using kpi, where xsale and ysale are columns in my data.

sayyam005
Contributor
Contributor
Author

this doesnt work ,i think because sum needs to be applied on some measure like sum({<condition>}Sales)

sunny_talwar

Would you be able to share a sample to understand what you have and also to understand what you need?

sayyam005
Contributor
Contributor
Author

i have attached qvf . i have created a measure where if xsale not null , ysale not null and xsale > ysale than 1 otherwise 0. there are 12 such cases but answer is 60

i want 12.

sunny_talwar

You already had it working in your table, right? Try this

Sum(Aggr(If(IsNull (Max(ySale)) OR IsNull( Max(xSale)) OR Max(xSale) <= Max(ySale), 0, 1), BusinessDate, xSale, ySale))

sayyam005
Contributor
Contributor
Author

thanks ,was confused.