Flag for the last day in the month

[josecanalla]

Hello, I am using QlikSense. I want to get the last day of every month. I read to use a flag to set the last day in the month; but this not work for me because I am not sure that the last day of the month is in my data. For example:

Date	Last day in month?
01/01/2016	No
...	...
30/01/2016	Yes
01/02/2016	No
...	...
29/02/2016	Yes

January has 31 days. But the day 31 is not in my data, so I want to mark the day 30 as the last. Or the day 29, 28, etc... I want to mark the last day of the month "in my data".

How can I do?

Thanks!

[MK9885]

Hi,

If you've a Master Calendar you can use

if(Day(MonthEnd(YourDateFieldHere))=Day(YourDateFieldHere),1,0)

as MonthEndDates,

Or you can also use

if(Month(MonthEnd(YourDateFieldHere))=Day(YourDateFieldHere),1,0)

as MonthEndDates,

These 2 expressions will give you Flag as 1 or 0.

monthend(YourDateField)

as [Calendar Month End Date]

The above script will give you all your MonthEndDates.

And

Maybe you can try same in your Date Table (if you do not have Master Calendar)

Either way using the above expression in Edit Script will give you Month End Date. In order to get month end as per your Date field, you've to use your field only and not Master Calendar Dates.

[marcohadiyanto]

Hi Jose,

you can use this script in your data load editor.

if(Date = monthend(Date), 'yes','no') as Monthendflag

Regards

[sunny_talwar]

I would probably add a floor function for this kind of comparison because MonthEnd() function returns a timestamp

If(Date = Floor(MonthEnd(Date)), 'Yes', 'No') as MonthEndFlag

[josecanalla]

The problem is that I am not sure if the monthend date is present in my data. For example, the last day of january maybe 2016-01-30 and not 2016-01-31. Or day 29. In that case what could I do?

[josecanalla]

Please see my reply to @arvind654

[josecanalla]

Please see my reply to @arvind654

[sunny_talwar · Accepted solution]

You should be able to use Peek/Previous to do this in the script. Make sure to order your load by date in descending order

Peek() vs Previous() – When to Use Each

LOAD....

     If(Month(Date) = Previous(Month(Date)), 0, 1) as Flag

Resident ....

Order By Date DESC;

[MK9885]

That's is why you've to use your Date field, not a Temporary Date field from Master Calendar.

Take a resident load from your table where you've date field coming from Data Base (which has dates maybe only until Jan 25th/May20th as month end.)

Resident_Table:

Load

if(Day(MonthEnd(YourDateFieldHere))=Day(YourDateFieldHere),1,0)as MonthEndDates,

If(Date = Floor(MonthEnd(Date)), 'Yes', 'No') as MonthEndFlag

Resident YourDatefieldTableName;

You can use either of the expressions Yes or No as Flag OR 1,0 as Flag.

[josecanalla]

Simple, and it works perfectly. Thanks!