9 Replies Latest reply: Nov 20, 2015 3:13 PM by Ciro ORIC

# KPI: Formula For Count Highers than Average

Hi there!

I need your help to create a KPI formula that returns the number of years with Sum(Amout) above the average.

My data serie is:

AggregationScope:

Year Quarter Amount

2012 Q1 1000

2012 Q1 1000

2012 Q2 1700

2012 Q3 1400

2012 Q4 1800

2013 Q1 1000

2013 Q2 1300

2013 Q3 1100

2013 Q4 1400

] (delimiter is ' ');

Best regards,

Ciro

• ###### Re: KPI: Formula For Count Highers than Average

Number of years of Number of QuarterYears above average?

• ###### Re: KPI: Formula For Count Highers than Average

Hi Sunny,

I want number of years.

Best regards,

Ciro.

• ###### Re: KPI: Formula For Count Highers than Average

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 Avg2012
Below Avg2013

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 Avg20121
Below Avg20131
• ###### Re: KPI: Formula For Count Highers than Average

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

• ###### Re: KPI: Formula For Count Highers than Average

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

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

Hi CIRO,

Have a play around with this doc.

Kind regards

Andrew

• ###### Re: KPI: Formula For Count Highers than Average

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

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