Lock down of dynamic buckets

[madhur4qlik]

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.

[madhur4qlik · Accepted solution]

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

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)<=vHigh2, vBucket2,

     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

      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)<=vHigh3, vBucket3,

       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

       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)<=vHigh4, vBucket4,

         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

         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)<=vHigh5, vBucket5,

           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

           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)<=vHigh6, vBucket6,

              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

               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)<=vHigh7, vBucket7,

                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

                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)<=vHigh8, vBucket8,

                   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

                    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)<=vHigh9, vBucket9,

                     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

                     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)<=vHigh10,vBucket10           

                     ))))))))))