topic Re: Date function in QlikView

Date function

Wed, 25 Nov 2020 16:16:04 GMT

for ex if my StartDate is 8/1/2014 and Frequency num is 12 what is the output from the below expression ? What exactly it is doing ?

if((Date(MonthEnd(AddMonths(StartDate,FrequencyNum*iterno()-1)),'YYYY-MM-DD'))

Re: Date function

sunny_talwar — Tue, 31 May 2016 18:32:57 GMT

I think this would create a list of dates like this:

2014-08-31

2015-08-31

2016-08-31

2017-08-31

and will stop based on your While Loop

Re: Date function

MarcoWedel — Tue, 31 May 2016 19:59:08 GMT

Hi,

it wouldn't do much because it's not a valid expression.

You failed to post the missing then-expression (and optionally the else-expression too).

The condition as well might not be complete because to interpret the expression you posted numerically would not require the formatting date() function.

If you posted the complete LOAD statement you might get better answers.

hope this helps

regards

Marco

Re: Date function

Gabriel — Tue, 31 May 2016 20:41:06 GMT

Hi,

What this expression is doing is as follow

AddMonths(StartDate,FrequencyNum*iterno()-1)

The definition of AddMonth is as follows, AddMonths(StartDate,n,Mode), the Nth represent Month. So FrequencyNum * Iterno() -1, FrequencyNum equals 12 multiply by IterNo(). Imagine the IterNo() is (which is Number of iteration, i.e 1 or 2 or 3, suggest Loop) so FrequencyNum * iterno() will evalute to 12 * 1 (or 12 * 3. Whatever the IterNo() is) the final result could be 12 and then the -1 will give a final output of 11 for the Month.

Let's put everything together.

AddMonths(StartDate,FrequencyNum * IterNo()-1) is

AddMonths ('2003-01-29',11) results to AddMonths ('2003-11-29'). Then the MonthEnd Function comes in

MonthEnd(AddMonths ('2003-11-29') ) results to 2003-11-30 which basically return the month end date. Finally format the result as 'YYYY-MM-DD'. We don't kow what the If function is doing so I can't comment on that.

Hope this helps