topic Re: how to generate dates backwards using addmonths function ? in QlikView

how to generate dates backwards using addmonths function ?

Fri, 20 Nov 2020 18:02:30 GMT

The scenario is like this,

if i select today's date i.e 15-06-2015 the i need to generate 12 number of dates in backward manner for each month, for example

for date 15-06-2015, it should generate-

15-05-2015

15-04-2015

15-03-2015

15-02-2015

15-01-2015

15-12-2014

15-11-2014

15-10-2014

15-09-2014

15-06-2014

Thanks.

Re: how to generate dates backwards using addmonths function ?

giakoum — Mon, 15 Jun 2015 11:53:10 GMT

generate where? in your reload script? Day(Yourdate) & Month(Yourdate)-iter() & Year(yourdate) should do it with a while statement. Just format it correctly

Re: how to generate dates backwards using addmonths function ?

Mon, 15 Jun 2015 12:03:26 GMT

hello,

when i select a particular date in a calendar table then i need to display that 12 dates in a textbox.

thanks

Re: how to generate dates backwards using addmonths function ?

giakoum — Mon, 15 Jun 2015 12:05:02 GMT

my suggestion is that you generate those dates in your reload script as I explained above and then use concat to display them in the text object

What is the purpose of that actually?

Re: how to generate dates backwards using addmonths function ?

sunny_talwar — Mon, 15 Jun 2015 12:08:22 GMT

Do you want to display something like this?

Try this (I used Today(), but you can use Max(DateField))

=Date(Today() -1) & Chr(13) &

Date(Today() -2) & Chr(13) &

Date(Today() -3) & Chr(13) &

Date(Today() -4) & Chr(13) &

Date(Today() -5) & Chr(13) &

Date(Today() -6) & Chr(13) &

Date(Today() -7) & Chr(13) &

Date(Today() -8) & Chr(13) &

Date(Today() -9) & Chr(13) &

Date(Today() -10) & Chr(13) &

Date(Today() -11) & Chr(13) &

Date(Today() -12)

Re: how to generate dates backwards using addmonths function ?

giakoum — Mon, 15 Jun 2015 12:10:16 GMT

Sunindia what you suggest works only for today.

Ajinkya wants : when i select a particular date in a calendar table then i need to display that 12 dates in a textbox.

Re: how to generate dates backwards using addmonths function ?

giakoum — Mon, 15 Jun 2015 12:11:01 GMT

and it is not the previous 12 day, it is the previous 12 months with that date

Re: how to generate dates backwards using addmonths function ?

sunny_talwar — Mon, 15 Jun 2015 12:12:43 GMT

My bad, just realized you have Date as DD/MM/YYYY and I think you needed last 12 months like this:

Expression:

=Date(AddMonths(Today(), -1), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -2), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -3), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -4), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -5), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -6), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -7), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -8), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -9), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -10), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -11), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -12), 'DD/MM/YYYY')

Re: how to generate dates backwards using addmonths function ?

burli1895 — Mon, 15 Jun 2015 12:13:34 GMT

Look at this: Change Date by Button

Re: how to generate dates backwards using addmonths function ?

sunny_talwar — Mon, 15 Jun 2015 12:13:50 GMT

We just need to replace Today() with Max(DateField) to make it work for the selection, isn't that true? and Yes I realized that he wanted to go back 12 months instead of 12 dates, so I changed my expression in the below post.

Re: how to generate dates backwards using addmonths function ?

Mon, 15 Jun 2015 12:14:06 GMT

no , actually i want to decrease the 'MM' value , not the 'DD' value.

thanks.

Re: how to generate dates backwards using addmonths function ?

sunny_talwar — Mon, 15 Jun 2015 12:15:25 GMT

Give this a try and use Max(YourDateField) instead of Today()

=Date(AddMonths(Today(), -1), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -2), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -3), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -4), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -5), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -6), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -7), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -8), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -9), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -10), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -11), 'DD/MM/YYYY') & Chr(13) &

Date(AddMonths(Today(), -12), 'DD/MM/YYYY')

Re: how to generate dates backwards using addmonths function ?

giakoum — Mon, 15 Jun 2015 12:16:27 GMT

ifit is only one selected date

otherwise it will not work

Re: how to generate dates backwards using addmonths function ?

sunny_talwar — Mon, 15 Jun 2015 12:18:09 GMT

Not true, Max(Date) will work in multiple selections as well. (Note: I am not using Only(Date))

Re: how to generate dates backwards using addmonths function ?

Mon, 15 Jun 2015 12:19:27 GMT

Hi ,

isn't there a more efficient way to do it ?

thanks

Re: how to generate dates backwards using addmonths function ?

giakoum — Mon, 15 Jun 2015 12:19:29 GMT

yes but if I as a user select 2 dates, I expect to it work for both, not only the max

I all comes down to what Ajinkya is trying to achieve here...

Re: how to generate dates backwards using addmonths function ?

sunny_talwar — Mon, 15 Jun 2015 12:22:17 GMT

Yup, that is absolutely true

It ultimately comes down to what ajinkya333 wants to achieve.

Re: how to generate dates backwards using addmonths function ?

Mon, 15 Jun 2015 12:22:52 GMT

actually user will select only one date at a time.

Re: how to generate dates backwards using addmonths function ?

Mon, 15 Jun 2015 12:27:39 GMT

can we use iterno function somehow ?

Re: how to generate dates backwards using addmonths function ?

Anonymous — Mon, 15 Jun 2015 12:29:26 GMT

Hi,

I do two solutions.

The first with object calendar and the second with listbox.

I hope this can help you.

Regards

/Giuseppe