topic Re: Summing negative Values in Buckets using Aggr(If(Sum)) in App Development

Summing negative Values in Buckets using Aggr(If(Sum))

mrthomasshelby — Sun, 28 May 2017 11:02:39 GMT

Hello all,

I'm trying to create some buckets with the following expression:

Aggr(If(Sum([Value in USD]) < 0, Dual('<0',1),

If(Sum([Value in USD]) < 500, Dual('0-500',2),

If(Sum([Value in USD]) < 2500, Dual('500-2.5k',3),

If(Sum([Value in USD]) < 5000, Dual('2.5k-5k',4),

If(Sum([Value in USD]) < 20000, Dual('5k-20k',5),

If(Sum([Value in USD]) < 50000, Dual('20k-50k',6),

If(Sum([Value in USD]) < 250000, Dual('50k-250k',7),

If(Sum([Value in USD]) < 500000, Dual('250k-500k',8),

If(Sum([Value in USD]) < 1000000, Dual('500k-1Mn',9)

Dual('>1Mn',10)))))))))), [PO#]).

As you can see, the first bucket needs to sum up all the PO# that have negative values. I have tried using the above expression but all I get is a bar with the label '-'. I think this is because of the negative values in my data. How do I proceed to achieve this? TIA!

stalwar1‌

Re: Summing negative Values in Buckets using Aggr(If(Sum))

swuehl — Sun, 28 May 2017 16:04:06 GMT

I assume you are using this as calculated dimension in a bar char, right?

What is the expression you are using (should be an aggregation function)?

=Count(DISTINCT [PO#])

Could you post a small sample QVW that demonstrates your issue?

Re: Summing negative Values in Buckets using Aggr(If(Sum))

sunny_talwar — Sun, 28 May 2017 17:46:27 GMT

I don't see anything wrong with your calculated dimension.... (except may be if you have a full stop at the end?)

Aggr(If(Sum([Value in USD]) < 0, Dual('<0',1),

If(Sum([Value in USD]) < 500, Dual('0-500',2),

If(Sum([Value in USD]) < 2500, Dual('500-2.5k',3),

If(Sum([Value in USD]) < 5000, Dual('2.5k-5k',4),

If(Sum([Value in USD]) < 20000, Dual('5k-20k',5),

If(Sum([Value in USD]) < 50000, Dual('20k-50k',6),

If(Sum([Value in USD]) < 250000, Dual('50k-250k',7),

If(Sum([Value in USD]) < 500000, Dual('250k-500k',8),

If(Sum([Value in USD]) < 1000000, Dual('500k-1Mn',9)

Dual('>1Mn',10)))))))))), [PO#]).

If that is just for the post and you don't have it otherwise, then I would check the casing of your field names.... if those are also not the issue, then a sample or screenshots might be helpful in seeing the issue

Re: Summing negative Values in Buckets using Aggr(If(Sum))

mrthomasshelby — Mon, 29 May 2017 06:32:45 GMT

Hello! Thanks for the reply. Yes, I'm using it as a calculated dimension for the Bar chart. The expression I'm using is Sum([Value in USD]) as I want to display the Dollar Value split within these buckets. PFA the excel file and the qvf.

Re: Summing negative Values in Buckets using Aggr(If(Sum))

mrthomasshelby — Mon, 29 May 2017 06:33:24 GMT

Hello.PFA the excel file and qvf in the reply above. Thanks!

Re: Summing negative Values in Buckets using Aggr(If(Sum))

swuehl — Mon, 29 May 2017 08:49:27 GMT

There is a comma missing before the last Dual() function, which the syntax checker has not catched

Aggr(If(Sum([Value in USD]) < 0, Dual('Less than 0',1),

If(Sum([Value in USD]) < 500, Dual('0-500',2),

If(Sum([Value in USD]) < 2500, Dual('500-2.5k',3),

If(Sum([Value in USD]) < 5000, Dual('2.5k-5k',4),

If(Sum([Value in USD]) < 20000, Dual('5k-20k',5),

If(Sum([Value in USD]) < 50000, Dual('20k-50k',6),

If(Sum([Value in USD]) < 250000, Dual('50k-250k',7),

If(Sum([Value in USD]) < 500000, Dual('250k-500k',8),

If(Sum([Value in USD]) < 1000000, Dual('500k-1Mn',9) , //missing comma

Dual('>1Mn',10)))))))))), [PO#])

Re: Summing negative Values in Buckets using Aggr(If(Sum))

mrthomasshelby — Mon, 29 May 2017 09:16:47 GMT

My bad! Sorry for the foolish question then. Should have caught this easily.