
Re: KPI: Formula For Count Highers than Average
Sunny Talwar Nov 20, 2015 8:54 AM (in response to Ciro ORIC)Number of years of Number of QuarterYears above average?

Re: KPI: Formula For Count Highers than Average
Ciro ORIC Nov 20, 2015 12:50 PM (in response to Sunny Talwar )Hi Sunny,
I want number of years.
Best regards,
Ciro.


Re: KPI: Formula For Count Highers than Average
Andrew Walker Nov 20, 2015 9:55 AM (in response to Ciro ORIC)Try this:
Create a straight table with dimension
=Aggr(if(sum(TOTAL <Year> Amount) > Avg(TOTAL Aggr(sum(Amount),Year)),'Above Avg', 'Below Avg'),Year)
and with this expression
Concat(DISTINCT Year,', ')
And you get this.
Compare with Avg Year Above Avg 2012 Below Avg 2013 Add more years' data and the expression will list all the years separated by a ','.
add a further expression count(DISTINCT Year) and you get
Compare with Avg Year Count of Year Above Avg 2012 1 Below Avg 2013 1 
Re: KPI: Formula For Count Highers than Average
Anand Chouhan Nov 20, 2015 9:25 AM (in response to Ciro ORIC)Hi,
May be you are looking for this
Please check the attached Qlikview file or you can calculate the KPI formula on the script part as well as.
Regards,
Anand

KPI.qvw 152.8 K


Re: KPI: Formula For Count Highers than Average
Andrew Walker Nov 20, 2015 9:46 AM (in response to Ciro ORIC)If it's just a simple count of the number of years that are above avg this will give that figure:
=Count(Aggr((if(sum(Amount) > Avg(TOTAL Aggr(sum(Amount),Year)),1)),Year))

Re: KPI: Formula For Count Highers than Average
Ciro ORIC Nov 20, 2015 12:43 PM (in response to Andrew Walker)Hi, Andrew! Thank you for reply.
=Count(Aggr((if(sum(Amount) > Avg(TOTAL Aggr(sum(Amount),Year)),1)),Year))
returns 0 independent of the dimension selection.
Any suggestion?
Best regards,

Re: KPI: Formula For Count Highers than Average
Andrew Walker Nov 20, 2015 1:30 PM (in response to Ciro ORIC)Hi CIRO,
Have a play around with this doc.
Kind regards
Andrew

Re: KPI: Formula For Count Highers than Average
Ciro ORIC Nov 20, 2015 3:13 PM (in response to Andrew Walker)Hi Andrew ! Thank you to reply.
This following formula answered my question:
Sum(Aggr(if(Sum(TOTAL <Year> Amount) > Avg(TOTAL Aggr(Sum(Amount),Year)),1,0),Year))
Best regards,
Ciro.




Re: KPI: Formula For Count Highers than Average
nagarjuna k Nov 20, 2015 1:52 PM (in response to Ciro ORIC)Hi try this Exp:
It will gives you how may years are >Avg(Amount) .
Expression:
=if(sum(Amount)> Avg(Amount) ,Count(DISTINCT Year))
Regards,
Nagarjuna