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: 
josecanalla
Creator
Creator

Flag for the last day in the month

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:

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

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!

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

9 Replies
MK9885
Master II
Master II

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
Partner - Specialist
Partner - Specialist

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
Creator
Creator
Author

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
Creator
Creator
Author

Please see my reply to @arvind654

josecanalla
Creator
Creator
Author

Please see my reply to @arvind654

sunny_talwar

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
Master II
Master II

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
Creator
Creator
Author

Simple, and it works perfectly. Thanks!