Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
))))))))))
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
))))))))))