- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Help needed with aggr function in chart - Qlik Sense
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!