Seeking Guidance on Basing Deciling (Fractile) to be based on Volume Sum NOT Count of Records

[#1]

Request:

I am looking for guidance on how to create a Calculated Decile Dimension that is based on Total Volume (See attached - first table: DESIRED OUTPUT:  Quartile Based on Volume).   In that table chartable Chart, there are 4 Quartile Segments.   For this view, I am basing the Quartile Segment based on Volume .  Such that each Quartile Segment represents 25% of the total volume.

The Data Table Chart is based on Raw Data Sheet in the attached excel.

Issue using Fractile Function:

Unfortunately within Qlikview, if I use the Fractile function, it bases the Percentile/Quartile on the Customer Count. In other words, each Quartile segments represents 25% of the total Customer Count.   (See attached ACTUAL Ouput: using Fractile Fuction is based on Customer Count).

Reference Source: http://www.qlikfix.com/2010/10/08/decile-analysis/

if(Profit <= fractile(TOTAL Volume, 0.75), 'Quartile 4 (100% to 75%)',
if(Profit <= fractile(TOTAL Volume, 0.50), 'Quartile 3 (75% to 50%)',

if(Profit <= fractile(TOTAL Volume, 0.25), 'Quartile 3 (50% to 25%)',

'Quartile 1 (25% to 0%)')))

I also attempted to use  aggr function with Rank to created the quartile but still no luck. 

aggr(if(rank(sum(Volume))<0.75*count(total CustID), 'Quartile 4 (100% to 75%)',
if(rank(sum(Volume))<0.5*count(total CustID), 'Quartile 3 (75% to 50%)',
if(rank(sum(Volume))<0.25*count(total CustID), 'Quartile 3 (50% to 25%)',
'Quartile 1 (25% to 0%)'))),Customer #)

[swuehl]

I think it will be quite hard to create a calculated dimension based on your requirements.

If you don't need to be selection sensitive, I think creating the classification in the script should be possible.

You can look into Henric's recipe on how to create a chart expression for classification.

Recipe for a Pareto Analysis

[swuehl]

You can also the forum for some few examples for ABC analysis or pareto charts, and Christof shows a quite nice solution here:

ABC Analysis to set and remember classification at runtime

Using the expression field from the ABC list box as dimension in a chart may give you a starting point.

This solution however needs a button and the pareto select actions to work.

[#4]

Thanks, I will review the reference link accordingly.

[#5]

The link "recipe for Pareto Analysis" describes the workaround solution that I used.  I tried that out and it did work as expected.  The process that Henric Cronström proposes is of course more efficient than than the one I employed.  The current issue I am having with that, is that the expression that I am using to calculate the "Volume" is inefficient. 

It requires a set analysis that is based only on 2 dimension.  Thus I am using a Set Analysis that is passing everything and then setting it to 2 modifiers (Time and Business Unit).

Sum({1<Dimension1=$::Dimension1, Dimension2 =$:: Dimension2>} Dimension3)

Dimension 1 = Time Period

Dimension 2 = Business Unit

Dimension 3 = Volume Sold

The only other solution I can think of is to create a set analysis that includes dimensions that we want to "Ignore" which could be up to 20+ dimensions.  

[#6]

Hi,

Quick question regarding your suggestion on "Script," by script do you mean creating the classification during the loading?

I reviewed the the Recipe for Pareto... and even tried to create a calculated dimension based on the suggested solution and I was able to create the the dimension with the correct volume breakout, however it is not working as expected due to the issue described in these discussion:

RangeSum Aggr - How to use new row count

Aggr: sort value by dimension

Essentially there is no way to accumulate the volume on the correct sort order.

Thanks in advance,

Jon

[swuehl]

The current issue I am having with that, is that the expression that I am using to calculate the "Volume" is inefficient.

It requires a set analysis that is based only on 2 dimension.  Thus I am using a Set Analysis that is passing everything and then setting it to 2 modifiers (Time and Business Unit).

Sum({1<Dimension1=$::Dimension1, Dimension2 =$:: Dimension2>} Dimension3)

Dimension 1 = Time Period

Dimension 2 = Business Unit

Dimension 3 = Volume Sold

The only other solution I can think of is to create a set analysis that includes dimensions that we want to "Ignore" which could be up to 20+ dimensions. 

Sorry, I haven't fully understood your setting and what you are trying to do. Could you upload a small sample QVW?

[swuehl]

Essentially there is no way to accumulate the volume on the correct sort order.

Yes, the advanced aggregation aggr() has one severe limitations, the dimension values within the aggr() function are sorted by load order asc, no way to change that as far as I know.

'By script', I indeed was referring to a classification during load, so this will not be selection sensitive.

[#9]

Basically, the volume based KPI has to be fixed on certain modifiers.  I have 2 options, either create a Set Analysis with "1{" ignores all selections and then add modifiers or create a set analysis that passes all selection (default) "$}"

1) Ignore All

=sum({1<TimerPeriod=$::TimePeriod, BusinessUnit=$::BusinessUnit>} VolumeSold)

2_Consider All Selection with Ignore Modifiers

=sum( ${

<DimIgnore1=

,DimIgnore2=

,DimIgnore3=

,DimIgnore4=

...

,DimIgnore20=

>}

VolumeSold)

I tested this out and concluded that option 2 is more is efficient in terms of calculation time.

Best,

Jon

[#10]

Thanks for the confirmation.  I am regrouping with our team to see what our next steps are.  We are actually considering pre-defining the classification/decile groups at the datamart level.   This is what what we have done in our current legacy system.  Essentially these pre-defined groups are static and based on those two dimension (Business Unit and Time Measure)...