Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm new to QlikSense and I'm trying create a set analysis to get the sum of amounts of the max day of each month.
I have a table with 2 columns Date and Amount. Each month can have a value for amount each day or only a few days. My problem is that I need to display the sum of amount for max available day of each month.
I created a new column YearMonth and I was able to create a measure to get the max day of each Month with this:
max({<Date=>} aggr(Max(Date), YearMonth))
When I try to use this set analysis in a sum it always bring 0:
sum({<Date={$(=Max({<Date=>} Aggr(Max(Date), YearMonth)))}>} Amount)
Could someone please help?
Check if firstsorted value can work for you -
Load *,Month(Date)&'-'&Year(Date) as [Month-Year] inline [
Date, Value
01/01/2023, 100
01/10/2023, 100
01/15/2023, 300
02/01/2023, 100
02/10/2023, 100
03/01/2023, 100
03/10/2023, 100
04/01/2023, 100
04/10/2023, 100
];
Sum(Aggr(firstsortedvalue(Value,-Date),[Month-Year]))
Thanks,
Check if firstsorted value can work for you -
Load *,Month(Date)&'-'&Year(Date) as [Month-Year] inline [
Date, Value
01/01/2023, 100
01/10/2023, 100
01/15/2023, 300
02/01/2023, 100
02/10/2023, 100
03/01/2023, 100
03/10/2023, 100
04/01/2023, 100
04/10/2023, 100
];
Sum(Aggr(firstsortedvalue(Value,-Date),[Month-Year]))
Thanks,