Help needed with aggr function in chart - Qlik Sense

[liviumac]

Hello dear Qlik community,

would very much appreciate your help with the following situation

I have a data structure as per below

field 1 = date (there are several dates)

field 2 = customer id (there are several customers as of each date)

field 3 = transaction id (there are several transactions for each customer as of each date)

field 4 = Value 1 (1 or more for each transaction, so the transaction id may appear more than once for each customer))

field 5 = Value 2 (1 or more for each transaction, so the transaction id may appear more than once for each customer)

I need to calculate, for each customer as of each date, the [minimum between sum(Value 1) and sum(Value 2)], i.e.disregarding transactions

for this, I use this expression

aggr(if(sum(Value 1)>sum(Value 2),sum(Value 2),sum(Value 1)),customer id)

then, I need to calculate, for each reporting date, the sum of these minimum values for all the customers as of each date, so obviously I use

sum(aggr(if(sum(Value 1)>sum(Value 2),sum(Value 2),sum(Value 1)),customer id))

this works fine

now what I want to do is create a visualisation (a bar chart) that plots, for each date, the sum of the measure described above

now here is the problem:

I create a bar chart using as dimension the date and as the measure the expression above, i.e. sum(aggr(if(sum(Value 1)>sum(Value 2),sum(Value 2),sum(Value 1)),customer id))

but the displayed values for each date are incorrect: some dates show very large values, some very small values

what dazzles me is that if I select one single date (using a filter), the calculation is always correct, but if more than one date is selected, the result is unusable

thank you

L

[brunobertels]

Hi Liviu

May be you need to take the date a second criteria in your Aggr function

sum(aggr(if(sum(Value 1)>sum(Value 2),sum(Value 2),sum(Value 1)),date,customer id))

Bruno

[rubenmarin]

Hi Liviu, using Aggr() only by customerid in a chart whit a dimension like date can lead to this kind of issues, try adding your chart dimension to the Aggr() funtion:

sum(aggr(if(sum(Value 1)>sum(Value 2),sum(Value 2),sum(Value 1)),date, customer id))

Also maybe this expression gives better performance:

sum(aggr(RangeMin(sum(Value 1), sum(Value 2)),date, customer id))

[jagan]

HI,

Try adding date dimension in the Aggr() like below

sum(aggr(if(sum(Value 1)>sum(Value 2),sum(Value 2),sum(Value 1)),customer id, date))

Hope this helps you.

Regards,

Jagan.

[liviumac]

thank you very much - that obviously worked flawlessly !

what I did not know was that the AGGR function accepts more than 1aggregation criteria (date AND customer, in my case)... I was trying to use 2 AGGR functions, which did not work because nested aggregations are not allowed (except when using the TOTAL qualifier, but that was not helpful in my case, because it aggregated accross all dates)

thank you once again, this is a fantastic piece of software!