Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
byrnel0586
Creator
Creator

AddMonths function

Hi,

I am needing to have 4 text objects showing the most recent 4 reporting dates. Our reporting dates are the last day of each month, so for example I need them to show 5/31/16, 4/30/16, 3/31/16, & 2/29/16. I am able to get the most recent reporting date by using the below formula, but when I try to use the AddMonths function to get the previous month, it is showing the same day value. So, it's showing 5/31/16 & 4/31/16 when I need it to be 4/30. Anyone know how to get the last day of each month to populate?

=If(Only(Year),Date(Max(ReportDate)), 'M/D/YYYY')

=If(Only(Year), Date(AddMonths(Max(ReportDate), -1), 'M/D/YYYY'))

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

Here it is,

=If(Only(Year), Date(MonthEnd(AddMonths(Max(ReportDate), -13)), 'M/D/YYYY'))

View solution in original post

8 Replies
tamilarasu
Champion
Champion

May be this.?


=If(Only(Year), Date(MonthEnd(AddMonths(Max(ReportDate), -1)), 'M/D/YYYY'))

byrnel0586
Creator
Creator
Author

Thank you, that did work for what I had asked. However, I forgot to mention that I need the year to be the year prior... is that possible? Instead of 4/30/16 I need 4/30/15.

trdandamudi
Master II
Master II

May be as attached:

tamilarasu
Champion
Champion

Linda

You can try below expression.

=If(Only(Year), Date(MonthEnd(AddYears(Max(ReportDate), -1)), 'M/D/YYYY'))

byrnel0586
Creator
Creator
Author

Thank you, I did try that but I will still need the previous month's date as well. So when the year 2017 is selected I will need the max date with the previous year (4/30/16), but also need the date of the month prior (3/31/16).

tamilarasu
Champion
Champion

Here it is,

=If(Only(Year), Date(MonthEnd(AddMonths(Max(ReportDate), -13)), 'M/D/YYYY'))

byrnel0586
Creator
Creator
Author

Thank you very much!

tamilarasu
Champion
Champion

My pleasure. Have a nice day.