Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sum Aggr with condition

Hi guys,

How can I use a condition on the following expression?

SUM(AGGR(SUM(DISTINCT Points), TransID))

the condition i want to apply is {$<[Date_ID.autoCalendar.Year]={'2016'}>}

I tried like this:

SUM({$<[Date_ID.autoCalendar.Year]={'2016'}>} AGGR(SUM(DISTINCT Points), TransID))

but it didn't work.


Can you help me?

Thanks in advance,

Miguel Cunha

Labels (1)
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Anand, Thanks for your help.

I tryed it like this =SUM( {$<Year={'2016'}>} AGGR(distinct Points, TransID))

and I think it gave me the correct result (I'm going to check with sql right now).

View solution in original post

12 Replies
swuehl
MVP
MVP

its_anandrjs

Can you try this

=SUM(DISTINCT {$<[Date_ID.autoCalendar.Year]={'2016'}>}  AGGR(Points,TransID))

Anonymous
Not applicable
Author

Thanks for your anwser but that's not the result i'm looking for.

I want to Sum the points but the points for each TransID can only be counted once and then I want to apply the condition.

To help explaining, I have a Table (QVD) with my sales. Each sale has a TransID. However a sale has multiple rows, one for every product sold in that especific sale and I only want to count the points for that sale once.

its_anandrjs

Share some data to have a look, May be you have to use count.

Anonymous
Not applicable
Author

Anand,

The data I'm working with is confidencial.

Here is an excel with some sample of the data.

As I told you, for each TransID, I just want to sum the points once.

To do that I can use SUM(AGGR(SUM( DISTINCT Points), TransID)).

But how can I filter the sum to the year 2016?

Thanks again

its_anandrjs

Check the attached may be this

Anonymous
Not applicable
Author

Anand, sorry fot this question but once I'm using Qlik Sense, can I open that file you sent? How can I do it?

its_anandrjs

Use this code though change the drive location and source

LOAD TransID,

     ID_Line,

     Product,

     Price,

     Points,

     Date,Year(Date) as Year

Expre:-=SUM(DISTINCT  {$<Year={'2016'}>} AGGR(Points,TransID))

swuehl
MVP
MVP

Miguel,

if you say 'it didn't work', can you please detail what you see and what you expect to see?

In general, the usage of set analysis in combination with advanced aggregation is explained in Henric's blog post I've linked to in my first answer.

Also check that the set analysis itself is working fine, for example check using a table chart with TransID and TransDate as dimensions and

SUM({$<[Date_ID.autoCalendar.Year]={'2016'}>}  Points)


as expression that the filter works as expected. There are / were some issues with the automatic generated calendar fields and set analysis.