Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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.
Hi Jose,
you can use this script in your data load editor.
if(Date = monthend(Date), 'yes','no') as Monthendflag
Regards
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
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?
Please see my reply to @arvind654
Please see my reply to @arvind654
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;
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.
Simple, and it works perfectly. Thanks!