Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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?

Anonymous
Not applicable
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"

Anonymous
Not applicable
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.

Anonymous
Not applicable
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?

Anonymous
Not applicable
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))

Anonymous
Not applicable
Author

thanks ,was confused.