topic Re: Pareto method implementation on QlikView 11 (plugin) in QlikView

Pareto method implementation on QlikView 11 (plugin)

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I am new here and i began training on QlikView. I had some issues that i want you if you do not mind to help me.

I want to implement Pareto method in a chart on QlikView and for each product which class (A, B or C) it belongs to so i have to do these following steps:

Classify the products volume in descending order ( from the highest to the smallest).
Calculate the percentage of each product volume comparing to the total volume of all products.
Calculate the cumaltive percentage of each product beginning with the first product ( the product that it has the highest volume) (the cumulative percentage will be cremented).
Plot Pareto line.
Identifiy for each product which class it belongs to:

The products that constitute 80% of the total volume will be affected to the class A (cumulative percentage between 0% and 80%).

After that, the products that make us reach 95% of the total volume will be affected to the class B ( they correspond to cumulative percentage values between 80% and 95%)

The rest of products will be affected to the class C.

I will show you now the chart that i created in the figure below.

The second column represent the products and the third column represent the products volumes.

The forth column "CUMUL PERCENTAGE" is the column where we are going to calculate the cumulative percentage of product volumes.

I am not familiar with QlikView expression functions that's why i cannot calculate the cumulative but i know how mathematically:

after sorting volumes ( the first step that i already do it), we take the first product "SPH" cumul percentage of this product is its volume devided by the total volume of all products, we put the result of this division in the first empty cell in column 4 . Then, again, in column 4 , the second cell, its value will be the volume of the second product VX_PHYSIO_3 divided by the total volume, and the result will be added to the first result ( the value above).

But i cannot make this in QlikView.

I want to know also how to show only the product of class A and then products of class B,etc.

I hope that my issue is clear and it is very nice if you can help me.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

The second issue: how to get rid of undesirable values.

I tried here to calculate the forecast accuracy of products expressing in percentage. My aim is to deal with the products that have a bad accuracy ( less than 80%) so i want to let in my chart only the product that they have a bad accuracy and do not show products with accuracy upper than 80%.

How can I do, please?

THANK YOU.

Re: Pareto method implementation on QlikView 11 (plugin)

marcus_sommer — Thu, 21 Jun 2018 15:29:36 GMT

Take a look here: Recipe for a Pareto Analysis – Revisited

- Marcus

Re: Pareto method implementation on QlikView 11 (plugin)

sunny_talwar — Thu, 21 Jun 2018 15:37:35 GMT

Also, take a look here

ABC Analysis in Qlikview

Re: Pareto method implementation on QlikView 11 (plugin)

Anonymous — Thu, 21 Jun 2018 15:41:33 GMT

Hello,

thank you for answering. I took it a look and i cannot do the second step, i mean the sort by expression beacause i cannot choose it. This is what i have. Any help please? Another issue is that i did not know when i put the expression to color the Pareto class. Thank you for helping.

Re: Pareto method implementation on QlikView 11 (plugin)

Anonymous — Thu, 21 Jun 2018 15:59:45 GMT

Hello,

Re: Pareto method implementation on QlikView 11 (plugin)

Anonymous — Thu, 21 Jun 2018 16:56:42 GMT

Hello,

Re: Pareto method implementation on QlikView 11 (plugin)

marcus_sommer — Fri, 22 Jun 2018 06:52:42 GMT

If your dimension is part of a dimension-group you need to sort them there or to enable the latest option within your screemshot to overwrite the group sort order.

- Marcus

Re: Pareto method implementation on QlikView 11 (plugin)

Anonymous — Fri, 22 Jun 2018 08:16:07 GMT

Hello Marcus,

thank you for responding. If i understand you, i think it is a part of a dimension group. So can you explain to me how to enable the latest option?

Thank you,

Alâa Eddine

Re: Pareto method implementation on QlikView 11 (plugin)

marcus_sommer — Fri, 22 Jun 2018 09:44:10 GMT

This one:

Re: Pareto method implementation on QlikView 11 (plugin)

Anonymous — Fri, 22 Jun 2018 09:50:39 GMT

Hello,

i did it but i had an issue in Accumulate sales. The sales are not accumulated. Do you know why please?

Re: Pareto method implementation on QlikView 11 (plugin)

marcus_sommer — Fri, 22 Jun 2018 09:57:52 GMT

Probably your expression isn't right. I think it should be look like:

rangesum(above(sum(Sales), 0, rowno(total)))

- Marcus

Re: Pareto method implementation on QlikView 11 (plugin)

Anonymous — Fri, 22 Jun 2018 10:23:46 GMT

Hello Sir,

thank you very much. I chose a straight table instead of pivot one that's why the accumulative sales did not work properly.

The first problem is solved but i have another issue that i described after my Pareto issue. I wil describe it to you now if you do not mind:

""""

How can I do, please? """"

Thank you and best ragerds,

Alâa Eddine.

Re: Pareto method implementation on QlikView 11 (plugin)

marcus_sommer — Fri, 22 Jun 2018 11:06:23 GMT

You need to add this condition either in each expression with something like:

if(YourExpression < 0.8, YourExpression, null())

or within at least one dimension like:

if(aggr(YourExpression < 0.8, Dim1, Dim2), Dim1, null())

and then hiding NULL within this dimension and/or by the expressions (tab presentation).

Maybe more useful might be just to select the needed cluster-sizes with a listbox-expression like:

class(aggr(YourExpression, Dim1, Dim2), 0.2)

- Marcus

Re: Pareto method implementation on QlikView 11 (plugin)

Anonymous — Fri, 22 Jun 2018 12:45:22 GMT

Hello,

thank you but i did not understand the second formula: if(aggr(YourExpression < 0.8, Dim1, Dim2), Dim1, null()). What can dimensions be and how to hide null values ?

Excuse me, but i am still beginner.

Thank you for help.

Best regards, Alâa Eddine.

Re: Pareto method implementation on QlikView 11 (plugin)

marcus_sommer — Fri, 22 Jun 2018 13:19:26 GMT

Dim1 and Dim2 are here placeholder for the dimensions in whose context the measure should be calculated and a measure itself couldn't be a dimension else it always needs an aggr() to build a Calculated Dimensions.

Often the needed dimensions within the aggr are those ones which are used within the chart - in your case the year-field and your dimension-group - but depending on your calculation it might be further ones. The link above explained very well how the logic works.

- Marcus

Re: Pareto method implementation on QlikView 11 (plugin)

Anonymous — Fri, 22 Jun 2018 14:13:17 GMT

Hello,

i created a calculated dimension and i put the formula but i do not why it showed me an error.

This is the formula:

= If ((1- (sum (fabs(aggr(sum ([SalesActualSales])

, SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,SalesYearMonth) -

aggr(sum ([SalesForecastM-2]), SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,SalesYearMonth)))/sum(aggr(sum([SalesActualSales]),

SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,SalesYearMonth ))) < 0

or sum(aggr(sum([SalesActualSales]), SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD

,SalesYearMonth))= 0),null()

,If(1- (sum (fabs(aggr(sum ([SalesActualSales])

, SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,SalesYearMonth) -

aggr(sum ([SalesForecastM-2]), SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,SalesYearMonth)))/sum(aggr(sum([SalesActualSales]),

SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,SalesYearMonth )))<0.8,1- (sum (fabs(aggr(sum ([SalesActualSales]), SalesSubsidiaryCode_CD,

SalesAggregatedBrand_CD,SalesYearMonth)- aggr(sum ([SalesForecastM-2]), SalesSubsidiaryCode_CD, SalesAggregatedBrand_CD,SalesYearMonth)))

/sum(aggr(sum([SalesActualSales]), SalesSubsidiaryCode_CD,

SalesAggregatedBrand_CD,SalesYearMonth ))),null()))

The formula is correct beacause i used it to calculate accuracy values.

Thank you.

Re: Pareto method implementation on QlikView 11 (plugin)

marcus_sommer — Fri, 22 Jun 2018 14:28:02 GMT

The "expression" is not right because it is an expression and not a calculated dimension because it missed an outer aggr-function. If your expression already used one or several aggr() and maybe even nested - so it needs further to be wrapped within an aggr() to create a valid dimension.

Beside this I have the impression that there are too many not really needed aggr-functions. You should only use them if the results couldn't be calculated in another way and not as "standard" aggregation.

Please refer again to the above provided link to the calculated dimensions and this ones:

When should the Aggr() function NOT be used?

Pitfalls of the Aggr function

- Marcus

Re: Pareto method implementation on QlikView 11 (plugin)

Anonymous — Fri, 22 Jun 2018 14:54:10 GMT

Hello,

i am really beginner in qlik view and i did not understand very much the documents. Can you tell what are aggr that i can eliminated to correct the formula?

Thanks.

Re: Pareto method implementation on QlikView 11 (plugin)

marcus_sommer — Fri, 22 Jun 2018 15:30:05 GMT

Unfortunately I couldn't explain it better as the above provided postings do. Aggr() and deduced things like calculated dimensions aren't simple features (especially if nested contructs and/or multiple conditions with if-loops and/or set analysis are used) which could be understand in a few minutes (even for the advanced users) else you need some time to understand the logic behind it.

Often its useful to play with such features within a simple dummy-app and then transferring the knowledge to the real applications. Here another excellent posting how it worked and what should be considered to get it working: Q-Tip # 14 – AGGR() and Synthetic Dimensions | Natural Synergies.

- Marcus