# Qlik Sense App Development

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?

Miguel Cunha

Tags (4)
1 Solution

Accepted Solutions
Contributor

## Re: Sum Aggr with condition

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

Can you try this

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

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.

MVP

## Re: Sum Aggr with condition

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

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

MVP

## Re: Sum Aggr with condition

Check the attached may be this

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?

MVP

## Re: Sum Aggr with condition

Use this code though change the drive location and source

ID_Line,

Product,

Price,

Points,

Date,Year(Date) as Year

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

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.