Show only last day's data in table

[okin_pfa]

Hi,

I was trying to get the last day of the month's value to show in table.

Here is the table where I want to get that result.
I tried with following formulas but it didn't work:
- sum({<OnDay = {LastDay}>} TotalLength)
- if(OnDay = LastDay, sum(TotalLength), 0)

OnDay is my Date format of data.

 

This is the data that should be printed in the table instead of the yellow ones.

[canerkan]

Hi Niko_pfa,

EDIT:  I didn't  read that you want the last of the Months, sorry for that. To get your result i would recommend flagging your dates in your loadscript that are the last day of the month. You will have to add the following line to your load statemant that is loading your field "OnDate":

Table:
Load
.....,
If(OnDate = Date(Floor(MonthEnd(OnDate))), 1, 0) as flagMonthEnd
From.....

then you can use this set expression:

sum({<flagMonthEnd = {1}>} TotalLength)

++++++++++++++++++++++++

you could create a variable an use that in your set expression like this:

Assuming you always want to show the data of your last working day and working days are Monday to Friday. Add this line to your load script:

Let vYesterday = Date(Today(1)-if((1*weekday(Now(),0)) = 0, 3, 1),'DD.MM.YYYY'); //Or whatever formate your date has

otherwise add this line:

Let vYesterday = Date(Today()-1);

Then use this set expression:

sum({<OnDay = {"$(=vYesterday)"}>} TotalLength)

Let me know if it helped.

Regards

Can

[okin_pfa]

No, that's not what I would like to have. In this way, I have a display of data from yesterday only. 
I would like to have data from every last day of the month, something like this 

Month	Last Day	Sum(TotalLength) => display of data for the whole month	What i want, just display last day of month data
1	31.01.2022.	104 990 890	4 471 218
2	28.02.2022.	102 979 878	3 939 018
3	31.03.2022.	119 683 144	4 650 735
4	30.04.2022.	93 214 465	2 464943
5	31.05.2022.	108 356 144	2 967 961
6	30.06.2022.	103 374 507	3 731 245

[canerkan]

Hi Niko_pfa,

Have you tried this suggestion as well:

To get your result i would recommend flagging your dates in your loadscript that are the last day of the month. You will have to add the following line to your load statemant that is loading your field "OnDate":

Table:
Load
.....,
If(OnDate = Date(Floor(MonthEnd(OnDate))), 1, 0) as flagMonthEnd
From.....

then you can use this set expression:

sum({<flagMonthEnd = {1}>} TotalLength)