Qlik Community

Qlik Sense App Development

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

Not applicable

Script issue with an If statement

Hi there,

During the load process I'm assessing if a change in record occurs at the end of the month and if it does not force it to be the end of the previous month in a new column End Date Alt.

I've attempted to do this using an if statement.  I've added an extra column "Check" to assess why I'm having a problem.

See below:

[Data]:

Load *

    , if(

    date([End Date],'DD/MM/YYYY') = MonthEnd(date([End Date],'DD/MM/YYYY'))

  , date([End Date],'DD/MM/YYYY')

  , MonthEnd(addmonths([End Date],-1))

       ) as [End Date Alt]

    , MonthEnd([End Date]) as Check;

Load  * Inline [

'End Date'

31/10/2016,

04/10/2016,

29/02/2016

];

The if statement is not assessing the dates as I would have expected and I'm stumped.

The End Date matches the Check column for both the 29th and the 31st and yet the If statement does not resolve that way.  Clearly I'm doing something wrong. 

 

End DateEnd Date AltCheck
29/02/201631/01/201629/02/2016
04/10/201630/09/201631/10/2016
31/10/201630/09/201631/10/2016

Help!

1 Solution

Accepted Solutions
MVP
MVP

Re: Script issue with an If statement

MonthEnd(date [, shift = 0])

Returns a value corresponding to a timestamp with the last millisecond of the last date of the month containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the month which contains date. Negative values in shift indicate preceding months and positive values indicate succeeding months.

Examples:

monthend ( '2001-02-19' ) returns '2001-02-28' with an underlying numeric value corresponding to '2001-02-28 23:59:59.999'

monthend ( '2001-02-19', -1 ) returns '2001-01-31' with an underlying numeric value corresponding to '2001-01-31 23:59:59.999'

So you need to compare to

if(

    Floor([End Date]) = Floor(MonthEnd( [End Date])),

....

[Date() formatting is not needed in numerical comparisons]

3 Replies
MVP
MVP

Re: Script issue with an If statement

MonthEnd() function outputs a TimeStamp, I think you will need to use Floor() with it to give a desired output here:

[Data]:

Load *

    , if(

    date([End Date],'DD/MM/YYYY') = Floor(MonthEnd(date([End Date],'DD/MM/YYYY')))

  , date([End Date],'DD/MM/YYYY')

  , MonthEnd(addmonths([End Date],-1))

       ) as [End Date Alt]

    , MonthEnd([End Date]) as Check;

Load  * Inline [

'End Date'

31/10/2016,

04/10/2016,

29/02/2016

];

MVP
MVP

Re: Script issue with an If statement

MonthEnd(date [, shift = 0])

Returns a value corresponding to a timestamp with the last millisecond of the last date of the month containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the month which contains date. Negative values in shift indicate preceding months and positive values indicate succeeding months.

Examples:

monthend ( '2001-02-19' ) returns '2001-02-28' with an underlying numeric value corresponding to '2001-02-28 23:59:59.999'

monthend ( '2001-02-19', -1 ) returns '2001-01-31' with an underlying numeric value corresponding to '2001-01-31 23:59:59.999'

So you need to compare to

if(

    Floor([End Date]) = Floor(MonthEnd( [End Date])),

....

[Date() formatting is not needed in numerical comparisons]

Not applicable

Re: Script issue with an If statement

Thanks Both Floor did the trick.  I will have to read up on that a bit more