topic Re: AddMonths function in QlikView

AddMonths function

byrnel0586 — Mon, 20 Jun 2016 14:22:41 GMT

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'))

Re: AddMonths function

tamilarasu — Mon, 20 Jun 2016 15:53:18 GMT

May be this.?

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

Re: AddMonths function

byrnel0586 — Mon, 20 Jun 2016 16:06:34 GMT

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.

Re: AddMonths function

trdandamudi — Mon, 20 Jun 2016 16:19:21 GMT

May be as attached:

Re: AddMonths function

tamilarasu — Mon, 20 Jun 2016 16:28:14 GMT

Linda

You can try below expression.

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

Re: AddMonths function

byrnel0586 — Mon, 20 Jun 2016 16:40:30 GMT

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).

Re: AddMonths function

tamilarasu — Mon, 20 Jun 2016 16:52:41 GMT

Here it is,

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

Re: AddMonths function

byrnel0586 — Mon, 20 Jun 2016 16:55:10 GMT

Thank you very much!

Re: AddMonths function

tamilarasu — Mon, 20 Jun 2016 17:06:21 GMT

My pleasure. Have a nice day.