How to calculate the sum of values for max day of a month with set analysis?

[stanciuc]

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?

[Digvijay_Singh · Accepted solution]

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]))

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/BasicAggre...

Thanks,