Month End dates rolling data

[Sameer9585]

Hi,

i want the rolling 12 months trend in such a way that only count of that particular monthend only to be displayed.

Eg: if date 3rd march is selected,then
31st jan and 28/29 th feb count only to be displayed

Thanks

[Sameer9585]

Hi sunny,

Thanks for the reply, unfortunately it didn't work please refer to the
Excel file I have shared in that I have given the desired output also.

Thanks,

[sunny_talwar]

New Script

Table:
LOAD "date",
	 Month(date) as Month,
     sales
FROM [lib://dasd/com (1).xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Left Join (Table)
LOAD Month,
	 Max(date) as date,
     1 as MonthEndFlag
Resident Table
Group By Month;

New expression

Sum({<MonthEndFlag = {1}, date = {"<=$(=MaxString(date))"}>}sales)

[Sameer9585]

Hi sunny,

If the date selection is  3/22/2019 the the output should be like

1/31/2019	5
2/28/2019	7
3/22/2019	1

it should be dynamic to date selection.

Thanks,

[sunny_talwar · Accepted solution]

This is different then what you mentioned initially

Anyways, you can try this

Sum({<date = {"<=$(=MaxString(date))"}>} If(date = Aggr(NODISTINCT Max({<date = {"<=$(=MaxString(date))"}>}date), Month), sales))

[Sameer9585]

Hi sunny,

Sorry it is not working properly

[sunny_talwar]

No idea what you mean? For me this is what I see

No Selection

When 3/22/2019 is selected

What are you seeing?

[Sameer9585]

can you share the qvf file?

[sunny_talwar · Accepted solution]

I did that already