Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
kunkumnaveen
Specialist
Specialist

How to write a expression in KPI

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

Labels (1)
7 Replies
tresesco
MVP
MVP

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
Specialist
Specialist
Author

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

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
Specialist
Specialist
Author

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

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
Specialist
Specialist
Author

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
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.