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

[mrthomasshelby]

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‌

[swuehl]

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?

[sunny_talwar]

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

[mrthomasshelby]

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.

[mrthomasshelby]

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

[swuehl · Accepted solution]

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

[mrthomasshelby]

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