Qlik Community

Qlik Sense App Development

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

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

1 Solution

Accepted Solutions

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

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

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

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?

sayyam005
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"

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

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

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?

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

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

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

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

thanks ,was confused.

Community Browser