Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
ExMachina
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
Taoufiq_Zarra

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") 😉
ExMachina
Contributor II
Contributor II
Author

I want to see something like this.

 

Screenshot_1.jpg
I hope this is the right example.

aapurva09
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

ExMachina
Contributor II
Contributor II
Author

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.

ThePeterK
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
ExMachina
Contributor II
Contributor II
Author

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