How to write a expression in KPI

[kunkumnaveen]

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

[tresesco]

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))))

[kunkumnaveen]

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 

[tresesco]

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))))

[kunkumnaveen]

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

[tresesco]

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

[kunkumnaveen]

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 

[Brett_Bleess]

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