topic Re: Aggregation with set analysis in App Development

Aggregation with set analysis

dominiksobanski — Sun, 20 Aug 2023 10:49:48 GMT

Hello,

I am Iooking for expression wich give me a sum of sales for the first version of deal.

So something like below.

Sum({<version={"=$(min(version))"} >} amount)

But it has to take min version per deal, not the smallest from whole set of data.

I tried to achieve it with aggr function but no luck.

It has to be dynamic. User can choose eg. range of dates and the calculation has to work. So it is not always the same version of deal for the calculation.

Is it possible at all?

Re: Aggregation with set analysis

BrunPierre — Sun, 20 Aug 2023 13:53:17 GMT

Try

=Sum({$<version= {"=Min(TOTAL <deal> version)"}>} amount)

Re: Aggregation with set analysis

dominiksobanski — Mon, 21 Aug 2023 07:14:45 GMT

So I tried below:

=Sum({<version= {"$(=Min(TOTAL <deal> version))"}>} amount)

It is working only when one deal is selected.

For more it is taken only one value from the smallest version of all deals.

I need aggregation of amount for all deals for the smallest version for each of them.

Deal	Version	Amount
1	1	10
1	2	5
2	1	20
2	2	17
2	3	5
3	4	78
3	5	9
3	6	12

So for above table it should be 10 + 20 +78 = 108.

Re: Aggregation with set analysis

Rockstar7 — Mon, 21 Aug 2023 09:00:59 GMT

Try this

Sum({<Version={$(= concat(Distinct Aggr( Min(Version), Deal) , ',' ))}>}Amount)

Re: Aggregation with set analysis

robert99 — Mon, 21 Aug 2023 11:27:08 GMT

Hi @dominiksobanski

VersionSmallest: //

LEFT JOIN (TABLE) //or left keep or do a mapping load

load

Deal ,

firstValue(Version) as VersionSmallest

resident TABLE

GROUP BY Deal

order by Deal, Version;

Then use if(VersionSmallest = Version, etc

either in Script or a table.

NB In your example Min might work better as you don't need to Order by

Min - script function | Qlik Sense on Windows Help

Re: Aggregation with set analysis

marcus_sommer — Mon, 21 Aug 2023 12:02:04 GMT

Set analysis is a column-level evaluation but what you are describing as wanted is a row-level evaluation. This means it's not possible in this way else you will need an if-loop, like:

if(Version = min(Version), sum(Amount))

Re: Aggregation with set analysis

robert99 — Mon, 21 Aug 2023 13:15:48 GMT

Would this work?

if(Version = min(Version), sum(Amount))

Wouldn't it give the min(version) for all record not by Deal

Thats why doing the initial min in script is better. It will give the min by Deal

Likewise

Sum(if(Version = min(Version),Amount))

Also wouldn't work. Or does it if you have Deal as the only dimension??

I usually use script and a measure to achieve this

Re: Aggregation with set analysis

marcus_sommer — Mon, 21 Aug 2023 13:34:19 GMT

Of course the comparison needs to be done within the wanted dimensional context. This might be reached by including those fields within the dimensions of the object. If this isn't wanted this kind of context needs to be enforced, for example by an aggr().

Re: Aggregation with set analysis

dominiksobanski — Tue, 22 Aug 2023 06:08:29 GMT

I know that solution with flag would be simple and easy, but it is not possible due to lot of other dimensions connected with Deals in my model.

It has to be done dynamicaly in expression.

Re: Aggregation with set analysis

dominiksobanski — Tue, 22 Aug 2023 06:09:59 GMT

As I wrote in my first post I tried it but no luck.

Maybe you know how the expressino should look like with aggr() function?

Re: Aggregation with set analysis

marcus_sommer — Tue, 22 Aug 2023 06:13:10 GMT

Maybe with something like this:

sum(aggr(sum(Amount) * -(Version=min(Version)), Deal))

Re: Aggregation with set analysis

robert99 — Tue, 22 Aug 2023 11:34:00 GMT

@dominiksobanski

This video might help.

(562) How to use Aggregate (AGGR) chart function & First Sorted Value function in QLIK SENSE - YouTube

Re: Aggregation with set analysis

dominiksobanski — Fri, 25 Aug 2023 09:47:27 GMT

Thanks for proposal but it is not working.

Re: Aggregation with set analysis

dominiksobanski — Fri, 25 Aug 2023 09:57:16 GMT

Thank you for movie but it didn't answer on my problem.

Re: Aggregation with set analysis

robert99 — Fri, 25 Aug 2023 13:26:24 GMT

OK. It worked for me (much to my surprise) in a similar example to the one you described. Where I had previously used script. But good that you found a solution