Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Summing negative Values in Buckets using Aggr(If(S...

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

mrthomasshelby

Creator III

2017-05-28
07:02 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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,475 Views

1 Solution

Accepted Solutions

swuehl

MVP

2017-05-29
04:49 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,196 Views

6 Replies

swuehl

MVP

2017-05-28
12:04 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

MVP

2017-05-28
01:46 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,196 Views

mrthomasshelby

Creator III

2017-05-29
02:32 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,196 Views

mrthomasshelby

Creator III

2017-05-29
02:33 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,196 Views

swuehl

MVP

2017-05-29
04:49 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,197 Views

mrthomasshelby

Creator III

2017-05-29
05:16 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,196 Views

Community Browser