Excluding percentages in a KPI

[Simon4]

Hi all,

I have made this KPI:

avg(aggr(FirstSortedValue({<Netpurchaseprice={"<0>0"}>} Netpurchaseprice/Quantity,- Datecreated)/FirstSortedValue({<Netpurchaseprice={"<0>0"}>} Netpurchaseprice/Quantity,Datecreated)-1,Itemcode))

It shows the price difference from first order date to last order date. Due to administrational errors some percentages per product are for example 200 000%. I want to filter out all the percentages that are above 500%. Is this possible?

Thanks in advance,

Simon

[Oleg_Troyansky · Accepted solution]

Hi Simon,

Yes, you can! Simply use the IF function inside of the AGGR() to only include those values that are less than 5. Something like this:

avg(
     aggr( IF(
                    FirstSortedValue({<Netpurchaseprice={"<0>0"}>} Netpurchaseprice/Quantity,
                    -Datecreated)/FirstSortedValue({<Netpurchaseprice={"<0>0"}>} 
                    Netpurchaseprice/Quantity,Datecreated)-1 < 5,

               FirstSortedValue({<Netpurchaseprice={"<0>0"}>} Netpurchaseprice/Quantity,- Datecreated)/FirstSortedValue({<Netpurchaseprice={"<0>0"}>} Netpurchaseprice/Quantity,Datecreated)-1
               )
     ,Itemcode)
)

If you'd like to learn more advanced development techniques, including advanced Set Analysis and AGGR(), join us at the Masters Summit for Qlik , coming to New Orleans on Nov 14-16. 

Cheers,

[Simon4]

HI Oleg,

Thank you for the solution! I have a follow up question. I want to exclude 0 values and values of under -5 too. But the formula is not working. Is this possibile?

avg(
aggr( IF(
FirstSortedValue({<Netpurchaseprice={"<0>0"}>} Netpurchaseprice/Quantity,
-Datecreated)/FirstSortedValue({<Netpurchaseprice={"<0>0"}>}
Netpurchaseprice/Quantity,Datecreated)-1 >-5 and <5 and =0,

FirstSortedValue({<Netpurchaseprice={"<0>0"}>} Netpurchaseprice/Quantity,- Datecreated)/FirstSortedValue({<Netpurchaseprice={"<0>0"}>} Netpurchaseprice/Quantity,Datecreated)-1
)
,Itemcode)
)

Thank you for the invite, unfortunatly I will not be able to come 😞

Kind regards,

SV

[Simon4]

Ignore my last comment. I have got it working. 

Cheers