topic How to use aggr() function with max date in App Development

How to use aggr() function with max date

Stevendf — Tue, 09 Apr 2019 18:34:11 GMT

I am new to Qlik Sense and have a simple problem that I couldn´t find any answers that fit my case:

I have a table like this:

Date_Sales	Value
31/01/2019	10
31/01/2019	20
31/01/2019	30
28/02/2019	20
28/02/2019	30
28/02/2019	40
31/03/2019	15
31/03/2019	25
31/03/2019	35

I want to show in a KPI the sum of sales of the last date. In this case, last date is 31/03/2019 and the sum is 75.

The function that I came up was "=Max(aggr(sum([Value]),[Date_Sales.autoCalendar.YearMonth]))", but it´s, obviously incorrect, and only shows me the sum of the date 28/02/2019, which is 90 (max of the 3).

Any help for this? Sorry if it´s too simple. I started working on Qlik Sense this week and couldn´t find anything that helped me.

Thanks

Re: How to use aggr() function with max date

Vegar — Tue, 09 Apr 2019 19:01:08 GMT

RawData:
load  date#(Date_Sales, 'DD/MM/YYYY') as Date_Sales,
Value as Value 
inline [
Date_Sales,	Value
31/01/2019,10
31/01/2019,20
31/01/2019,30
28/02/2019,20
28/02/2019,30
28/02/2019,40
31/03/2019,15
31/03/2019,25
31/03/2019,35
];

With the script above you could use the following expression to get your 75.

=Sum({<Date_Sales = {"$(=Maxstring(Date_Sales))"}>}Value)

Re: How to use aggr() function with max date

dplr-rn — Tue, 09 Apr 2019 19:01:40 GMT

try
Sum({<Date_Sales={'$(=Max(Date_Sales))'}>}Value)

Re: How to use aggr() function with max date

Stevendf — Tue, 09 Apr 2019 19:10:56 GMT

It worked perfectly. Thank you so much.