Count no.of times one value is greater than other

[Anonymous]

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.

[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]

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]

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]

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]

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 · Accepted solution]

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]

thanks ,was confused.