Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
josecanalla
Not applicable

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!

Tags (1)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

Re: Flag for the last day in the month

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;

9 Replies
Khan_Mohammed
Not applicable

Re: Flag for the last day in the month

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.

chooco_co
Not applicable

Re: Flag for the last day in the month

Hi Jose,

you can use this script in your data load editor.

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

Regards

sunny_talwar
Not applicable

Re: Flag for the last day in the month

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
Not applicable

Re: Flag for the last day in the month

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
Not applicable

Re: Flag for the last day in the month

Please see my reply to @arvind654

josecanalla
Not applicable

Re: Flag for the last day in the month

Please see my reply to @arvind654

sunny_talwar
Not applicable

Re: Flag for the last day in the month

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;

Khan_Mohammed
Not applicable

Re: Flag for the last day in the month

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
Not applicable

Re: Flag for the last day in the month

Simple, and it works perfectly. Thanks!