show values for last date of each month in table qliksense

[Aspiring_Developer]

Hello,

I have below data :- 

I need to show the values only for last dates of each month like below :-

Trend_Date | Sum(Value)

30-09-2022 |10

31-08-2022| 20

How can I achieve this ? 

Thanks in advance

[rubenmarin · Accepted solution]

Hi, you can try with:

=If(MonthEnd(Trend_Date)=Trend_Date, Sum(Value))

It will be better if you have a calendar table in script, then you only need to flag the last aday of the month using the same if condition:

=If(MonthEnd(Trend_Date)=Trend_Date,1,0) as isLastDayOfMonth

And the expression could be:

Sum({<isLastDayOfMonth={1}>} Value)

[Aspiring_Developer]

Hello @rubenmarin 

Thanks for your response. I tried both the approaches but it didn't worked .Please see below:-

1)=If(MonthEnd(Trend_Date)=Trend_Date, Sum(Value))

2) 

[rubenmarin · Accepted solution]

Hi, sorry, monthend will need a floor to remove the time (or set it to 00:00:00)

Floor(MonthEnd(Trend_Date))=Trend_Date

[Aspiring_Developer]

Hello, I did the above:-

But it is only giving me the flag value as 0 and not 1. That is why the chart is coming blank. Please see below :-

Please correct me if I am doing something wrong.

Thanks

[Aspiring_Developer]

Thanks for your solution.

I did the change at backend like below :-

If(floor(MonthEnd(Trend_report_date))=Trend_report_date,1,0) as isLastDayOfMonth,

And then at front end did this :-

Thanks again