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

Announcements

July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases.
**IMPORTANT DETAILS**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: set analysis / aggr function

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

Zwax

Contributor III

2020-03-10
05:18 AM

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

set analysis / aggr function

Hi gurus

I am having some set analysis / aggr function issues. I am trying to sum some specific values.

Conditions:

- If Area = 34 and total amount within S_ID and Item is under 10
- Then sum amount only for Type AB

There are multiple items and other areas. For the simplicity in the sample, I kept only one.

I want to show Date, Item and the calculation.

As you can see in the sample the total sum of all Type AB’s is 45. However, the 2 lines marked with yellow should not be counted in the calc, since the total amount is above 10 (total and not type specific). The result should be 29 (45-12-4 = 29).

Test 3 gives the right result if I add the dimension of S_ID, but not without it.

Something in Test 2 do not calculate correct, if both types is present or in case of duplicates (the orange ones).

Expression:

Test 1: sum(if(aggr(Sum({<Area={34}>}Amount),S_ID,Item)<10,sum(if(Type='AB',Amount))))

Test 2: sum(if(aggr(Sum({<Area={34}>}Amount),S_ID,Item)<10 and Type='AB',Amount))

Test 3: if(aggr(Sum({<Area={34}>}Amount),S_ID,Item)<10,sum(if(Type='AB',Amount)))

Any ideas of how to accomplice this?

679 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2020-03-10
10:22 AM

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

Try this

`Sum(Aggr(If(Sum({<Area = {34}>} Amount) < 10, Sum({<Type = {'AB'}>} Amount)), Date, S_ID, Item))`

652 Views

2 Replies

sunny_talwar

MVP

2020-03-10
10:22 AM

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

Try this

`Sum(Aggr(If(Sum({<Area = {34}>} Amount) < 10, Sum({<Type = {'AB'}>} Amount)), Date, S_ID, Item))`

653 Views

Zwax

Contributor III

2020-03-11
03:02 AM

Author

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

@sunny_talwar wrote:Try this

`Sum(Aggr(If(Sum({<Area = {34}>} Amount) < 10, Sum({<Type = {'AB'}>} Amount)), Date, S_ID, Item))`

Nice. This solved the issue. Had to place the Area = {34} in the next sum as well to get the result I needed.

Sum(Aggr(If(Sum({<Area = {34}>} Amount) < 10, Sum({<Area = {34}, Type = {'AB'}>} Amount)), Date, S_ID, Item))

Thank you so much 😀👍

640 Views