Qlik Community

Ask a Question

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Displays the last date of each month

Good day everyone.

In the summary table, I made an output of the last date of the month and, accordingly, the output of data for the specified date. I also want to output the last current date, but I don't understand which one to implement.

If(floor(DATE) = floor(Monthend(max(DATE))), If(floor(DATE) = floor(max(DATE)), date(max(DATE)), date(DATE))) as DateEnd

Screenshot_1.jpgScreenshot_2.jpg

Labels (2)
6 Replies
Master II
Master II

Hi,

can you develop or give an example

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Contributor II
Contributor II

I want to see something like this.

 

Screenshot_1.jpg
I hope this is the right example.

Creator
Creator

There is a difference between using Monthend(max(Date)) and max(Date).

MonthEnd(max(date)): Gives you the month end date from the Month the Date belongs to. Date= 10.01.2020 then outcome 31.01.2020.

Max(Date): Gives you the maximum date available for your unique values used for grouping the table. for exp. Date has 2 values 10.01.2020 and 20.01.2020 for the unique record, output will be 20.01.2020.

So, in your case monthend is needed.

Thanks

Contributor II
Contributor II

Thank you for the explanation. 

But in my case, I do not know how to correctly paint the condition so that the last dates of the completed months and the last date of the current month are displayed, as in the screenshot above. When using MonthEnd, I only get what is shown in the first message.

Creator
Creator

I would use an if statement in my data load. If the month & year of the value in your date field are the same month & year as today, then use the maxdate, else use the monthend of the value in the date field.

93/93
Contributor II
Contributor II

Thanks for your advice

I tried using this formula:

If(Month(DATE)=Month(Today()) and Year(DATE)=Year(Today()), Date(Floor(max(DATE))), If(Floor(DATE)=Floor(Monthend(DATE)), date(DATE)))as DateEnd;

Аll the last months are displayed,as well as all the dates of the current month. Can you tell me what to correct in the condition to exclude the output of all dates for the current month?

Screenshot_1.jpgScreenshot_2.jpg