# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
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 ?

Labels (1)
• ### sunny_talwar

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

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

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

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

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

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

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

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 ?

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