topic Re: Create Buckets in Expression in App Development

Create Buckets in Expression

mrthomasshelby — Mon, 22 May 2017 12:17:47 GMT

Hello, I have a dataset that is as follows:

Vendor Sales PO#

A 1300 1

B 2200 2

A 1400 1

B 2100 3

C 2500 4

A 300 2

C 900 4

D 3200 5

B 2000 3

D 1500 5

E 1100 6

F 3500 7

E 1500 8

G 1400 9

H 600 10

H 900 10

G 1200 11

Now I'd like to create two Buckets based on Sum(Sales): one by Grouped by vendor and the other Grouped by PO#. The buckets should be as follows:

<5k,5k-50k,50k-100k,100k-250k,250k-500k,500k-1Mn. I tried creating a dimension like this:

Aggr(If(Sum([Sales]) > 0 and Sum([Sales]) < 5000, Dual('5k or Below',1),

If(Sum([Sales]) < 50000,Dual('5k-50k',2),

If(Sum([Sales]) < 100000, '50k-100k',Dual('50k-100k',3),

If(Sum([Sales]) < 250000,'100k-250k',Dual('100k-250k',4),

If(Sum([Sales]) < 500000, '250k-500k',Dual('250k-500k',5),

Dual('500k-1Mn',6)))))),[Vendor]) for Vendor Buckets

and

Aggr(If(Sum([Sales]) > 0 and Sum([Sales]) < 5000, Dual('5k or Below',1),

If(Sum([Sales]) < 50000,Dual('5k-50k',2),

If(Sum([Sales]) < 100000, '50k-100k',Dual('50k-100k',3),

If(Sum([Sales]) < 250000,'100k-250k',Dual('100k-250k',4),

If(Sum([Sales]) < 500000, '250k-500k',Dual('250k-500k',5),

Dual('500k-1Mn',6)))))),[PO#]) for PO buckets.

But it seems to not work as only one bar with '-' as dimension is being displayed. If you could correct me or give a way to create such buckets, I'd be grateful. TIA!

stalwar1 loveisfail

Re: Create Buckets in Expression

sunny_talwar — Mon, 22 May 2017 12:24:19 GMT

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

mrthomasshelby — Mon, 22 May 2017 12:40:18 GMT

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!

Re: Create Buckets in Expression

daveamz — Mon, 22 May 2017 12:44:00 GMT

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

Re: Create Buckets in Expression

sunny_talwar — Mon, 22 May 2017 12:46:12 GMT

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('5k-50k',2),

If(Sum([Sales]) < 100000, Dual('50k-100k',3),

If(Sum([Sales]) < 250000, Dual('100k-250k',4),

If(Sum([Sales]) < 500000, Dual('250k-500k',5), Dual('500k-1Mn',6)))))), [Vendor])

and

Aggr(If(Sum([Sales]) < 5000, Dual('5k or Below',1),

If(Sum([Sales]) < 50000, Dual('5k-50k',2),

If(Sum([Sales]) < 100000, Dual('50k-100k',3),

If(Sum([Sales]) < 250000, Dual('100k-250k',4),

If(Sum([Sales]) < 500000, Dual('250k-500k',5), Dual('500k-1Mn',6)))))), [PO#])

Re: Create Buckets in Expression

mrthomasshelby — Mon, 22 May 2017 12:47:16 GMT

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?

Re: Create Buckets in Expression

mrthomasshelby — Mon, 22 May 2017 12:50:06 GMT

Yes, got it! Thanks a lot again!

Re: Create Buckets in Expression

mrthomasshelby — Mon, 22 May 2017 14:14:46 GMT

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 — Mon, 22 May 2017 14:17:45 GMT

Try this

Aggr(If(Sum({1}[Sales]) < 5000, Dual('5k or Below',1),

If(Sum({1}[Sales]) < 50000, Dual('5k-50k',2),

If(Sum({1}[Sales]) < 100000, Dual('50k-100k',3),

If(Sum({1}[Sales]) < 250000, Dual('100k-250k',4),

If(Sum({1}[Sales]) < 500000, Dual('250k-500k',5), Dual('500k-1Mn',6)))))), [Vendor])