Aggregation with set analysis

[dominiksobanski]

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? 

[BrunPierre]

Try

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

[dominiksobanski]

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.

[Rockstar7 · Accepted solution]

Try this

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

[robert99]

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

[marcus_sommer]

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

[robert99]

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

[marcus_sommer]

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

[dominiksobanski]

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.

[dominiksobanski]

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?