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: 
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
swuehl
MVP
MVP

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]

View solution in original post

3 Replies
sunny_talwar

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

];

swuehl
MVP
MVP

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
Author

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