
Re: Create Buckets in Expression
Sunny Talwar May 22, 2017 8:24 AM (in response to Phalgun Parvathaneni)Would you be able to share the document where you have been trying this (just to save some rework on our end)

Re: Create Buckets in Expression
Phalgun Parvathaneni May 22, 2017 8:40 AM (in response to Sunny Talwar )Hello, PFA the dataset.
PS: This is only a part of the entire dataset and I cannot share the entire dataset due to obvious reasons! Thanks!

Sample.xlsx 10.2 K

Re: Create Buckets in Expression
Sunny Talwar May 22, 2017 8:47 AM (in response to Phalgun Parvathaneni)You have some typos in your calculated dimension.... try this
Aggr(If(Sum([Sales]) < 5000, Dual('5k or Below',1),
If(Sum([Sales]) < 50000, Dual('5k50k',2),
If(Sum([Sales]) < 100000, Dual('50k100k',3),
If(Sum([Sales]) < 250000, Dual('100k250k',4),
If(Sum([Sales]) < 500000, Dual('250k500k',5), Dual('500k1Mn',6)))))), [Vendor])
and
Aggr(If(Sum([Sales]) < 5000, Dual('5k or Below',1),
If(Sum([Sales]) < 50000, Dual('5k50k',2),
If(Sum([Sales]) < 100000, Dual('50k100k',3),
If(Sum([Sales]) < 250000, Dual('100k250k',4),
If(Sum([Sales]) < 500000, Dual('250k500k',5), Dual('500k1Mn',6)))))), [PO#])

261328.qvf 176.0 K

Re: Create Buckets in Expression
Phalgun Parvathaneni May 22, 2017 8:50 AM (in response to Sunny Talwar )Yes, got it! Thanks a lot again!

Re: Create Buckets in Expression
Phalgun Parvathaneni May 22, 2017 10:14 AM (in response to Sunny Talwar )Also, is there anyway I can use this Calculated Dimension as a filter in the filter pane option? I have tried putting in the same expression as dimension in the filter pane object but it shows no options. Thanks again!

Re: Create Buckets in Expression
Sunny Talwar May 22, 2017 10:17 AM (in response to Phalgun Parvathaneni)Try this
Aggr(If(Sum({1}[Sales]) < 5000, Dual('5k or Below',1),
If(Sum({1}[Sales]) < 50000, Dual('5k50k',2),
If(Sum({1}[Sales]) < 100000, Dual('50k100k',3),
If(Sum({1}[Sales]) < 250000, Dual('100k250k',4),
If(Sum({1}[Sales]) < 500000, Dual('250k500k',5), Dual('500k1Mn',6)))))), [Vendor])

261328.qvf 176.0 K






Re: Create Buckets in Expression
David Ambrozie May 22, 2017 8:44 AM (in response to Phalgun Parvathaneni)Hi,
Check the attach.
I strongly recommend you to try to calculate the bucket values in the script if that is possible. The aggr is a pretty heavy operation (depending of your full data set, of course)
Regards,
David

sample.qvw 155.0 K

Re: Create Buckets in Expression
Phalgun Parvathaneni May 22, 2017 8:47 AM (in response to David Ambrozie)Thanks! But the thing is these Buckets need to be dynamic according to the filters applied on the data. So I'm not sure if I can define them in the load script?
