topic Re: Sum Aggr with condition in App Development

Sum Aggr with condition

Anonymous — Sat, 16 Nov 2024 09:13:28 GMT

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

Re: Sum Aggr with condition

swuehl — Wed, 04 Oct 2017 08:54:00 GMT

Set Analysis in the Aggr function

Re: Sum Aggr with condition

its_anandrjs — Wed, 04 Oct 2017 08:54:14 GMT

Can you try this

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

Re: Sum Aggr with condition

Anonymous — Wed, 04 Oct 2017 09:02:47 GMT

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

its_anandrjs — Wed, 04 Oct 2017 09:10:05 GMT

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

Re: Sum Aggr with condition

Anonymous — Wed, 04 Oct 2017 09:24:37 GMT

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

its_anandrjs — Wed, 04 Oct 2017 09:30:51 GMT

Check the attached may be this

Re: Sum Aggr with condition

Anonymous — Wed, 04 Oct 2017 09:33:48 GMT

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

its_anandrjs — Wed, 04 Oct 2017 09:42:10 GMT

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

Re: Sum Aggr with condition

swuehl — Wed, 04 Oct 2017 09:44:15 GMT

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.

Re: Sum Aggr with condition

its_anandrjs — Wed, 04 Oct 2017 09:51:24 GMT

Attaching the Output

Re: Sum Aggr with condition

Anonymous — Wed, 04 Oct 2017 09:52:22 GMT

Hi Stefan,

Actually, I'm trying to calculate this in dashboard, in a table, with the Expression Editor.

I said "it didn't work" because the result was not correct.

Re: Sum Aggr with condition

Anonymous — Wed, 04 Oct 2017 10:24:09 GMT

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