Lock down of dynamic buckets

madhur4qlik — Fri, 20 Oct 2017 18:39:46 GMT

Hi All,

hope you all having good time.

I am bit stuck in a logic I created in calculation of dynamic buckets. I was required to calculate dynamic buckets for the count of Item Number under their respective percentiles as in how much percentage of the time a particular Item has been Traded in selected Trades.

Confusing...

let me give you an example

1. If an Item Item1 falls in 10-20 percentile in Trade 1 and Trade 2 but in Trade 3 it falls in 30-40 percentile than we will read Item1 will fall in 30 - 40 % with 33.3% in 10-20 percentile and 60-70 % with 66.66 % in 30-40 percentile.

2. It an Item Item1 falls in same percentile .. say 40-50 in all three selected Trades that it will be read as Item1 is 100% of the time traded in all three trades at 40 -50 percentile

3. If an Item Item1 falls in three different Percentile like 10-20, 20-30, 40-50 on selection of 3 Trades, it will read as 33.33 % of the time in all respective trades and percentiles.

Dynamic Buckets is subjected to selection of Trades.

Now the issue is I am using Calculated Dimension for bucketing and i am getting the correct Counts in the data grid as well but when when i am clicking on any count which is the intersection of Percentile and Bucket ("How Often") to drill down to the details of counts, my dynamic buckets recalculates and hampering the results.

So if I select all three Trades and select 4 under 80-90 percentile with 60 -70% time it should give details of 4 Items not sure why its giving me 5 Items.

Please find the QV and attached data set here as well.

Please feel free to let me a different approach as well.

Re: Lock down of dynamic buckets

madhur4qlik — Wed, 25 Oct 2017 14:11:28 GMT

It worked for me as i added a % key which is nothing but a concatenation of all the objects mention in Aggr.

in the script i added a column:

[Item Number]&[Item Percentile Band]&[Trades] as %Key.

Now include the same in your calculated expression like this..:

=if(Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)>vLow1

and Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)<=vHigh1, vBucket1,

if(Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)>vLow2

if(Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)>vLow3

if(Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)>vLow4

if(Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)>vLow5

if(Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)>vLow6

if(Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)>vLow7

if(Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)>vLow8

if(Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)>vLow9

if(Aggr(((100)/(Count(total<[Item Number]>[Item Percentile Band])))*(Count(total<[Item Percentile Band],[Item Number]>[Item Percentile Band])),%Key,[Item Percentile Band],[Item Number],Trades)>vLow10

))))))))))

topic Lock down of dynamic buckets in QlikView

Lock down of dynamic buckets

Re: Lock down of dynamic buckets