Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

miguelcunha
Contributor

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

1 Solution

Accepted Solutions
miguelcunha
Contributor

Re: Sum Aggr with condition

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).

12 Replies
MVP
MVP

Re: Sum Aggr with condition

Re: Sum Aggr with condition

Can you try this

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

miguelcunha
Contributor

Re: Sum Aggr with condition

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.

Re: Sum Aggr with condition

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

miguelcunha
Contributor

Re: Sum Aggr with condition

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

Re: Sum Aggr with condition

Check the attached may be this

miguelcunha
Contributor

Re: Sum Aggr with condition

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

Re: Sum Aggr with condition

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

MVP
MVP

Re: Sum Aggr with condition

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.


Community Browser