Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Sameer9585
Creator II
Creator II

Month End dates rolling data

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

 

17 Replies
Sameer9585
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Hi sunny,

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

1/31/20195
2/28/20197
3/22/20191

 

it should be dynamic to date selection.

Thanks,

sunny_talwar

This is different then what you mentioned initially

image.png

Anyways, you can try this

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

View solution in original post

Sameer9585
Creator II
Creator II
Author

Hi sunny,

Sorry it is not working properly
sunny_talwar

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

No Selection

image.png

When 3/22/2019 is selected

image.png

What are you seeing?

Sameer9585
Creator II
Creator II
Author

can you share the qvf file?

sunny_talwar

I did that already

image.png

View solution in original post