Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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))))

Highlighted
Specialist
Specialist

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 

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

Highlighted
Specialist
Specialist

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

Highlighted
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

Highlighted
Specialist
Specialist

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 

Highlighted
Digital Support
Digital Support

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.