Aggregating using an expression as a dimension

[derekjones]

Hi All

Hoping you can help me, I've got stuck on what I think is an aggregation issue. My issue is that I think in order to get the result I'm looking for I need to do an aggregation using one of the expressions I've already calculated in the table as a dimension value and I'm not sure if this is possible.

So to explain, I have sales (aka spend) data which is bucketed by margin value, below is a snippet...

What I want to do is 'bucket' this spend into 8 equal spend amounts then find the min margin for that bucket. So first I accumulate the spend and then put an accumulated percentage against that spend. Using that percentile I can tell which bucket it is in (as each bucket is 12.5%). So I now have a bucket of 0 to 7. All good so far...

I then want to use that 'Bucket' to find what the min Margin is for each bucket. This is where I am stuck as the 'Bucket' is a calculated field and so I can't use it in an aggregation. I've attached a qvw example where in the dataset I've added the bucket as part of the dataset to show it can be done when it is a dimension. But in my real data I don't have bucket and it has to be dynamically calculated based on selections so can't be done in the script.

My end goal is to try and just have a table with the 8 buckets and the bucket margin as per below..

Can anyone help please?

My thanks in advance

Derek

[Gysbert_Wassenaar]

I don't quite follow what you're after, but I'm guessing you're going to need the fractile function. See for example this blog post: Decile analysis - The Qlik Fix!

[derekjones]

Thanks Gysbert

From an initial glance at Barry Harmsen's blog it could be what I'm after. I'll let the community know if it works.

Thanks again for the point in the right direction.

Derek

[swuehl]

If I understood correctly, fractile() won't help here, it's really more like a pareto analysis with multiple bands.

(Well, maybe I am wrong) I believe the buckets won't show similar number of margin values.

I think you can use an advanced aggregation to create your 0-7 buckets based on Margin field (you need to have a margin field to make this approach work, no expression for margin calculation. I understood you have a margin field, right?).

I struggled a bit with your data showing some records with no numeric margin, in your sample it's sorted last in the table, so I tried to do the same (note if you remove these records from your selection or if you order these records first in your table for accumulated spend, the found min margin will of course change).

The solution attached works in QV12 using the new StructuredParameter for aggr() dimension sorting.

The sortable Aggr function is finally here!

It should also work with QV11.20, if you manage to sort the load order of margin field accordingly (like discussed in ref. blog post).

Best,

Stefan

[swuehl · Accepted solution]

Attached the QV11.20 version of the advanced aggregation.

I noticed that the load order of your margin field values is already sorted ascending, so if you manage to create your input table like you already did for this sample, sorted by margin ascending, you are done.

If your source differs and you need to sort in QV, you can do i like

YourSource:
LOAD Margin, 
     Spend, 
     [Accum Spend], 
     Percentile, 
     Bucket
FROM

(biff, embedded labels);


SORT:
LOAD *, Margin as MarginSorted
RESIDENT YourSource
ORDER BY Margin;


DROP TABLE YourSource;
DROP FIELD Margin;
Rename Field MarginSorted to Margin;

[derekjones]

Thanks Stefan

Yes you were right, the fractile(), although an interesting concept in itself, would not have solved my problem. Your solution works a treat with the data I provided and unfortunately we've not yet upgraded to 12 so I will have to sort the data in the load. I've done some initial tests and it seems to hang together.

One question, in your expression for the dimension, you have used 'Sum(TOTAL Spend)-1E-10', what is the purpose of -1E-10 doing as I've not seen this before and I can't work it out?

Thanks again for your assistance.

Derek

[swuehl]

It subtracts 10 to the power of -10, a very small number, from the quotient / percentage. because I noticed that the bucketing used in the dimension does create a bucket '8' otherwise, with the last margin value that fills up to 100%. I think this is a rounding issue, it should not do this logical wise.

AFAIR I used a different method in the other chart using RangeMin() to limit the max bucket number to 7. Since this does only affect bucket 7+, maybe this is the preferred method to cope with the rounding issue.