Sum Aggr with condition

[Anonymous]

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

[swuehl]

Set Analysis in the Aggr function

[its_anandrjs]

Can you try this

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

[Anonymous]

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]

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]

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]

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.