Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
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!

stalwar1

1 Solution

Accepted Solutions
swuehl
MVP
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#])

2017-05-29 10_48_15-Sample (6).xlsx  [Geschützte Ansicht] - Excel.png

View solution in original post

6 Replies
swuehl
MVP
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?

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
Creator III
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.

mrthomasshelby
Creator III
Creator III
Author

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

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

2017-05-29 10_48_15-Sample (6).xlsx  [Geschützte Ansicht] - Excel.png

mrthomasshelby
Creator III
Creator III
Author

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