topic Re: How to write a expression in KPI in QlikView

How to write a expression in KPI

kunkumnaveen — Sat, 16 Nov 2024 00:53:49 GMT

Hello All,

I am trying to two values in two diff KPIs......

For a selected Year, Month:

1) Bottom 5% sum(d_op) in one KPI

2)Bottom 5% distinct count (Code_prod) where sum(d_op) >0,

For first requirement i tried below code ,which is not working

Sum(Aggr(

If(RangeSum(Above(Sum(d_OP)/Sum(TOTAL d_OP), 0, RowNo())) < 0.05, Sum(d_OP), 0)

, (Code_prod, (=Sum(d_OP),Asc))))

what am i missing in the above pareto expression ?

Please find attached sample data

Re: How to write a expression in KPI

tresB — Mon, 06 Apr 2020 10:20:51 GMT

May be just a case sensitivity issue (in red below):

=Sum(Aggr(

If(RangeSum(Above(Sum(d_OP)/Sum(TOTAL d_OP), 0, RowNo())) < 0.05, Sum(d_OP), 0)

, (Code_Prod, (=Sum(d_OP),ASCENDING))))

Re: How to write a expression in KPI

kunkumnaveen — Mon, 06 Apr 2020 11:00:21 GMT

HI,

Thanks ...For the first expression , How to write the seconds one in KPI

Which is count distinct Code_Prod which contributes <.05 and sum(d_op)>0

how to write above two conditions in set expression

Re: How to write a expression in KPI

tresB — Mon, 06 Apr 2020 11:15:14 GMT

Try including '{<Code_prod={"=Sum(d_OP)>0"}>}' in all sum()s in the expression, like:

Sum({<Code_prod={"=Sum(d_OP)>0"}>} Aggr(

If(RangeSum(Above(Sum( {<Code_prod={"=Sum(d_OP)>0"}>} d_OP)/Sum(TOTAL {<Code_prod={"=Sum(d_OP)>0"}>} d_OP), 0, RowNo())) < 0.05, Sum({<Code_prod={"=Sum(d_OP)>0"}>} d_OP), 0)

, (Code_Prod, (=Sum({<Code_prod={"=Sum(d_OP)>0"}>} d_OP),Asc))))

Re: How to write a expression in KPI

kunkumnaveen — Mon, 06 Apr 2020 11:39:57 GMT

HI,

I need to count them .....so i have changed ur expression.....

count(distinct {<material_code_prod={"=Sum(d_OP)>0"}>} Aggr(

If(RangeSum(Above(Sum( {<material_code_prod={"=Sum(d_OP)>0"}>} d_OP)/Sum(TOTAL {<material_code_prod={"=Sum(d_OP)>0"}>} d_OP), 0, RowNo())) < 0.05, Sum({<material_code_prod={"=Sum(d_OP)>0"}>} d_OP), 0)

, (material_code_prod, (=Sum({<material_code_prod={"=Sum(d_OP)>0"}>} d_OP),Asc))))

i think i miss something in above expression.........

Re: How to write a expression in KPI

tresB — Mon, 06 Apr 2020 11:55:41 GMT

May be:

count(distinct {<material_code_prod={"=Sum(d_OP)>0"}>} Aggr(

If(RangeSum(Above(Sum( {<material_code_prod={"=Sum(d_OP)>0"}>} d_OP)/Sum(TOTAL {<material_code_prod={"=Sum(d_OP)>0"}>} d_OP), 0, RowNo())) < 0.05, material_code_prod ~~, 0~~)

, (material_code_prod, (=Sum({<material_code_prod={"=Sum(d_OP)>0"}>} d_OP),Asc))))

Note: Remove else part of IF

Re: How to write a expression in KPI

kunkumnaveen — Tue, 07 Apr 2020 06:09:27 GMT

Hi,

I tried your First expression the Summation one in three different KPI...

1)KPI1(Bottom 5% Current Month):Actual Value(2300)---Output i am Getting (2300)-------------->Matching

Sum(Aggr(

If(RangeSum(Above(Sum(d_OP)/Sum(TOTAL d_OP), 0, RowNo())) <= 0.05,Sum(d_OP), 0)

, (material_code_prod, (=Sum(d_OP),Asc))))

2)KPI1(Bottom 5% 3 Month):Actual Value(8938)---Output i am Getting (8903)-------------->Matching

Sum({<Year,Month,Date={">=$(=date(monthstart(addmonths(max(Date),-2)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}Aggr(

If(RangeSum(Above(Sum(d_OP)/Sum(TOTAL d_OP), 0, RowNo())) <= 0.05,Sum({<Year,Month,Date={">=$(=date(monthstart(addmonths(max(Date),-2)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}d_OP), 0)

, (material_code_prod, (=Sum(d_OP),Asc))))

3)KPI1(Bottom 5% 6 Month):Actual Value(24583)---Output i am Getting (24387)-------------->Not Matching

Sum({<Year,Month,Date={">=$(=date(monthstart(addmonths(max(Date),-5)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}Aggr(

If(RangeSum(Above(Sum(d_OP)/Sum(TOTAL d_OP), 0, RowNo())) <= 0.05,Sum({<Year,Month,Date={">=$(=date(monthstart(addmonths(max(Date),-5)),'DD-MM-YYYY'))<=$(=date(((max(Date))),'DD-MM-YYYY'))"}>}d_OP), 0)

, (material_code_prod, (=Sum(d_OP),Asc))))

So the issue with 3 months and 6 months Summation ...What could be the reason ?

Please find attached sample data for your reference

Re: How to write a expression in KPI

Brett_Bleess — Wed, 29 Apr 2020 13:58:52 GMT

Have a look at the following Design Blog posts, believe they may provide some further assistance:

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis/ba-p/1468497

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-Pareto-Analysis-Revisited/ba-p/1473684

Regards,
Brett