16 Replies Latest reply: Dec 17, 2017 5:19 PM by Sunny Talwar

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

• ###### 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

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

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

• ###### 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"

• ###### 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.

• ###### 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?

• ###### 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

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

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

thanks ,was confused.

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

is there a way to show only those rows in table where the value is 1 but i dont want to show that column with 1,0 values ?

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

Uncheck 'Include Zero Value' under Add-ons -> Data handling

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

i dont want that calculated column(last) also with 1,0 values. if i remove that column everything will come.Thats what i told in my previous comment

Basically i want to show only those rows where xsale > ysale and either of them should not be null,for that i have created that column but i dont want to show that column

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

Will check back in a bit

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

Try with a calculated dimension

Dimension

=Aggr(If(IsNull (Max(ySale)) OR IsNull( Max(xSale)) OR Max(xSale) <= Max(ySale), Null(), Date(BusinessDate)), BusinessDate)

xSale

ySale

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

hi sunny,

i want help with one more issue. Suppose i select a date from BusinessDate filter, i want all the calculations (both for table and KPI )to be done for past 2 months from the date i select from filter.

Suppose i select 29/03/2017 so the kpi should should be calculated from 29/01/2017 to 29/03/2017

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

For some reason it doesn't seem to be working without an expression in the chart, but KPI seems to work with this