Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Pareto method implementation on QlikView 11 (plugin)

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:

  1. Classify the products volume in descending order ( from the highest to the smallest).
  2. Calculate the percentage of each product volume comparing to the total volume of all products.
  3. 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).
  4. Plot Pareto line.
  5. 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.

                                        Capture.PNG

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.


capture 2.PNG


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


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?

capture3.PNG




THANK YOU.

18 Replies
marcus_sommer

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

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

- Marcus

Anonymous
Not applicable
Author

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:

""""

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? """"

capture3.PNG

Thank you and best ragerds,

Alâa Eddine.

marcus_sommer

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

Anonymous
Not applicable
Author

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.

marcus_sommer

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

Anonymous
Not applicable
Author

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.

aaqq.PNG

Dimension.PNG

marcus_sommer

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

Anonymous
Not applicable
Author

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.

marcus_sommer

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