Announcements
cancel
Showing results for
Did you mean:
Creator III

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

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!

1 Solution

Accepted Solutions
MVP

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#])

6 Replies
MVP

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?

MVP

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

Creator III
Author

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.

Creator III
Author

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

MVP

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#])

Creator III
Author

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