Qlik Sense App Development

New 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.

Tags (3)
1 Solution

Accepted Solutions
MVP

Re: Count no.of times one value is greater than other

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

16 Replies

Re: Count no.of times one value is greater than other

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

MVP

Re: Count no.of times one value is greater than other

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

New Contributor

Re: Count no.of times one value is greater than other

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"

New Contributor

Re: Count no.of times one value is greater than other

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.

New Contributor

Re: Count no.of times one value is greater than other

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

MVP

Re: Count no.of times one value is greater than other

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

New Contributor

Re: Count no.of times one value is greater than other

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.

MVP