Intangible value errors with convoluted Aggr-Expression

[Sapphire]

Heya!

I have a question concerning the Aggr-Function in Qlik-Sense, which I assumed to understand. However Qlik does neither act as (I think) it should nor gives out values I understand. Furthermore I can´t reproduce the Issue with a smaller table I created just for that issue.

My Issue:

I have a table with specific Car Numbers (VIN), which may or may not be distinct.
To each VIN I have an Option-To-Do as well as an Option-Done-Value, which is >=0 or empty.
In Addition to each VIN I have a Mode of Transport.
If the Sum of Option-to-do and Option-Done is 0, the VIN has no options, otherwise it does (this will become important).

It might look like this:

VIN	Option-To-Do	Option-Done	Mode of Transport
VN1	1	0	Bahn
VN2	2	0	Bahn
VN2	1	1	Bahn
VN2	0	2	Bahn
VN3	0	3	LKW
VN4	2	0	LKW
VN4	0	2	LKW
VN5			Bahn
VN6			LKW

I Need (in a pivot table):

For each Mode of Transport I need the Sum of only the highest Option-Done-Value per VIN per Mode of Transport. Which means for Example:

LKW: Expected outcome: 5 (3 (VN3) + 2 (VN4) [Ignore VN4 - 0, since 0<4, Ignore VN6, since empty, Ignore VN1,2 and 6 since not LKW])

My approach:

Sum(Aggr(Max([OptionDone]),[VIN]))

 For my Example above that works just fine, but...

Practical Outcome and Error:

When putting this expression into a pivot table that sums up all VINs according to Mode of Transport and whether the VIN has an option ("Ja") or not ("Nein"), I see i.e. that we have 67k VINs with no Options that have in Sum 6,4k Options, which obviously makes no sense. (for clarification: A VIN with zero OptionDone and zero Option-to-do has no options, else they have options).

 

Interestingly, if I filter the pivot to only show me vins with no options, the result is this:

 

I tried to filter with other criteria as well, but I could not trace the error down nor find any logic as to why the values are as they are. All I understand so far is that the error most likely might be in the formula stated above.

Can anyone help me understanding this behaviour or tell me what I could do to avoid this problem? My experience tells me, that the main reason of this most likely isn't qlik, but me, so I want to hotfix myself, and a bit of help is appreciated 🙂

Faithful regards!
Sapphire

[SerhanKaraer]

Hello Sapphire,

When you nest aggregation functions, each one comes with a context. The inner context (max func.) is your calculation context which you don't want to be affected by user selections and the outer one (sum func.) is user context which users can use filtering data. Thus, you don't want your calculation context to be affected by selection state, you have to explicity state in your calculation context.

Sum(Aggr(Max({1} [OptionDone]),[VIN]))

[Sapphire]

Hello SerhanKaraer,

first of all, thank you for your reply, I appreciate your help 🙂

I understand your point and tried your suggestion, however this seems to just make the error visible even when the user is filtering. It does only - as intended - "shield" the expression from user selections. That however is not my problem.

To specify my issue: I don`t want the sum of OptionDone on VINs that should have o Options done to be 6.417 (Screenshot 1) in the first place. There should be nothing that can be counted/summed up, however, the expression somehow does. If I now filter for just the VINs with no Options (screenshot 2), then it gets me the 0, as should be expected.
This indicates for me that, as long as there is no filter active which actively filters out all the VINs with no option, my expression does indeed count VINs with options done even when it should not. Or technically, since the sorting in VIN with and without option does not happen in the expression itself, but in the pivot table, something goes wrong there. Does this make sense?

Just for my understanding, I want to describe how I think my expression works, maybe there might be the issue?

Sum(Aggr(Max({1}[OptionDone],[VIN]))
--> Aggregate all of the values  of the field "OptionDone" together (as in 1, 2, 4, not as in sum up), for each VIN
i.e. (refering to the example table above) for VN4: 0,2

Sum(Aggr(Max({1}[OptionDone],[VIN]))
--> Take only the Max-value for all Options done.
i.e. (refering to the example table above) for VN4: 2.  (The {1} basically says: Ignore user selections for the OptionDone-field)
(If this Max-expression would not be nested, but stood alone, I'd instead expect 3 as result, due to 3 being the max-value of the column "OptionDone".)

Sum(Aggr(Max({1}[OptionDone],[VIN]))
The Nesting now should give out all the max-values in "OptionDone" per VIN, so the full result I´d expect to be: 0,1,3,2 (For VN1,2,3,4)

Sum(Aggr(Max({1}[OptionDone],[VIN]))
--> Finally the Sum should simply be 0+1+3+2 =6.

I hope what I write makes sense when not seeing the data, it feels like this is a more complex thing than I thought.

[SerhanKaraer · Accepted solution]

Hello,

For your first screenshots, there can exist a problem with Aggr function. Let me explain this,

For your sample data, I added an Option field as your pivot table includes a Ja/Nein field. (I know it does not match your Option definition, but I did it on purpose to clarify a position.)

LOAD * INLINE [
VIN Option-To-Do Option-Done Mode of Transport Option
VN1 1 0 Bahn Nein
VN2 2 0 Bahn Nein
VN2 1 1 Bahn Ja
VN2 0 2 Bahn Ja
VN3 0 3 LKW Ja
VN4 2 0 LKW Nein
VN4 0 2 LKW Ja
VN5 Bahn Nein
VN6 LKW Nein
] (delimiter is '\t');

When you use "Mode of Transport" and "Option" as your dimension and "Sum(Aggr(Max([Option-Done]),[VIN]))" as your measure, you will see incorrect results for Bahn M.O.T & Nein Option. The reason for that Aggr function group records by VIN and relates softly with your physical model. For VN2, your measure calculates 2 as output. Then it joins your physical model with the first record where it sees as VN2, i.e. the second record in your source table with Option "Nein" value. Therefore, all your max Option-Done value for VN2 is shown under "Nein" option.

You must add Option in your aggregation context: Sum(Aggr(Max([Option-Done]),[VIN],Option))

You will end up with the pivot table below:

This "awkward" situation happens due to the order in your source table. In your source table for some records, the first row for a VN is an option with "Nein". As a general rule, when you do not force a realtionship rule in your Aggr context, your aggregation context should match your chart dimensions.

[Sapphire]

Hello,

first of all: Thanks! That seems to be the solution to my problem, now it works 🙂

As for the reason, why it works and just to see, if I get the logic that's behind your solution:

So basically, the max- and sum-parts in my old expression work, as they should. However, Aggr. happens afterwards and joins the result "simply" to the first VIN in the list. And if this first VIN happens to be a No, then the max-value identified just gets joined with that No. Am I correct so far?

And the additional "Option" you put in the expression now forces the Aggr-Fkn. to also take into account which "Option"-Value the highest Identified Value (2 for VN2) has and therefore it will be correctly displayed?

Thanks again for your help! I hope, I also correctly understood how the solution works.  🙂

Sincere regards!
Sapphire

[SerhanKaraer]

Hi Sapphire,

That's exactly the reason for uncorrect results. There was a difference in the aggr context and chart context, now they are aligned by matching the dimensions. The default tactic for unmatching contextes for aggr context and chart context is DISTINCT. In DISTINCT scenario, you may confront what you have experienced. In NODISTINCT scenario, the values are all inflated. It joins not the first records matching, but all records matching, in your case both "Ja" and "Nein" options. There may be use cases that this is a valid joining tactic.

However, as a rule of thumb the final tactic is applied, that is matching the contextes.

You may have further reading on help site:

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.htm

Regards,