Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i am wondering if someone can help with a new field in a calendar.
basically i want to create a field which flags the first Monday of July in each fiscal year.
so current Fiscal year (2018), i want a flag of 0 set against 03/07/2017
Last Fiscal Year (2017) Flag of -1 against 04/07/2016
Previous Year (2016) Flag of -2 against 06/07/2015
etc
can this be achieved easily, i know i can hard code it but i would prefer something automatic so its done as the fiscal calendar gets extended.
any help would be appreciated.
sample attached.
My bad, try this
LET vMaxEndDate = '09/10/2017';
Dates:
LOAD CALENDAR_DDMMYYYY,
CALENDAR_YEAR,
CALENDAR_MONTH,
CALENDAR_DAY,
CALENDAR_WEEK_NO,
WEEKDAY,
FISCAL_PERIOD,
FISCAL_YEAR,
FISCAL_PERIOD_NO,
FISCAL_WEEK_NO,
AutoNumber(FISCAL_WEEK_NO, 'Overall') as WeekNum,
CALENDAR_MONTH_TEXT
FROM DATES.qvd (qvd);
Left Join (Dates)
LOAD Date(CALENDAR_DDMMYYYY) as YearStartDateMonday,
FISCAL_YEAR
Resident Dates
Where WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8 and Month(CALENDAR_DDMMYYYY) = 7;
Max:
LOAD Max(WeekNum) as MaxWeekNum
Resident Dates
Where CALENDAR_DDMMYYYY <= '$(vMaxEndDate)';
LET vMaxWeekNum = Peek('MaxWeekNum');
LET vLoop = Floor($(vMaxWeekNum)/53);
DROP Table Max;
FOR i = 0 to $(vLoop);
TRACE $(vLoop);
LET vCalc = $(vMaxWeekNum) - ($(i)*53);
TRACE $(vCalc);
Table:
LOAD Date(Min(CALENDAR_DDMMYYYY)) as MinDate,
FISCAL_YEAR
Resident Dates
Where WeekNum = $(vCalc)
Group By FISCAL_YEAR;
NEXT i;
Left Join(Dates)
LOAD *
Resident Table;
DROP Table Table;
FinalDates:
LOAD *,
If(Month(CALENDAR_DDMMYYYY) >= 7 and CALENDAR_DDMMYYYY >= YearStartDateMonday and CALENDAR_DDMMYYYY <= MinDate, Year(YearStart(CALENDAR_DDMMYYYY, 0, 7)) - Year(YearStart(Today(), 0, 7))) as Flag
Resident Dates;
DROP Table Dates;
Hi Chris,
Could you try the below code please:
Dates:
LOAD CALENDAR_DDMMYYYY,
CALENDAR_YEAR,
CALENDAR_MONTH,
CALENDAR_DAY,
CALENDAR_WEEK_NO,
WEEKDAY,
FISCAL_PERIOD,
FISCAL_YEAR,
FISCAL_PERIOD_NO,
FISCAL_WEEK_NO,
CALENDAR_MONTH_TEXT
FROM
DATES.qvd
(qvd);
tmp:
Load
CALENDAR_YEAR,
CALENDAR_MONTH,
WEEKDAY,
min(CALENDAR_DAY) as [Flag First Monday]
Resident Dates
where CALENDAR_MONTH = 7 and WEEKDAY = 'Monday'
Group by CALENDAR_YEAR, CALENDAR_MONTH,WEEKDAY;
left join (Dates)
Load
CALENDAR_YEAR,
CALENDAR_MONTH,
WEEKDAY,
1 as [Flag First Monday]
Resident tmp;
drop table tmp;
May be try this
Dates:
LOAD CALENDAR_DDMMYYYY,
If(Month(CALENDAR_DDMMYYYY) = 7 and WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8, Year(Today()) - Year(CALENDAR_DDMMYYYY)) as Flag,
CALENDAR_YEAR,
CALENDAR_MONTH,
CALENDAR_DAY,
CALENDAR_WEEK_NO,
WEEKDAY,
FISCAL_PERIOD,
FISCAL_YEAR,
FISCAL_PERIOD_NO,
FISCAL_WEEK_NO,
CALENDAR_MONTH_TEXT
FROM DATES.qvd (qvd);
I would use the following approach directly within the master-calendar generation:
...
if(month(Date) = 7 and weekday(Date) = 0 and day(Date) < 8, 1 * year(Date) - 2018, null()) as Flag
...
whereby I would not really prefer to use null() as the else-value else I would use 0 instead and starting the flag-values by 1.
- Marcus
I just did it in Excel to confirm first, but you guys are too quick. I got the same as Sunny, it just needs changing the result around
Cheers
Luis
If(Month(CALENDAR_DDMMYYYY) = 7 and WeekDay(CALENDAR_DDMMYYYY) = 'Mon' and Day(CALENDAR_DDMMYYYY) < 8, Year(CALENDAR_DDMMYYYY) - Year(Today())) as Flag,
You are right, if -1 is needed, just need the change the order of subtraction
Hi all, thanks for your help, attached is another sample, i have it working so far, so i have the correct flags against the first Monday of July for each year in the calendar - thanks very much!
my goal here is to put a flag in the table for each of the dates stating which year it is from (0, -1, -2 etc).
so current YTD is 03/07/2017 - vMaxEndDate - which is 09/10/2017.
so i want a 0 against each of the dates in that period.
and the same for last year, the flag of -1 should be against 04/07/2016-10/10/2016 - note that this needs to be the Monday AFTER the variable date above (if the variable date (for the year in question) is not a Monday) - we are comparing like for like, i.e. mon - mon
does that make sense?
Is vMaxEndDate a variable which defines your Fiscal Year End Date? Does this change from year to year? Also, you want 0 for 03/07/2017 till 09/10/2017 and -1 for 04/07/2016 till 10/10/2016? But how is this YTD? I am a little confused?
Within the second half of this: How to use - Master-Calendar and Date-Values are various links how to create flags for various measures. They are mostly not designed for fiscal years but I think the logics could be adapted - especially if the fiscal/calendar dates are just regarded as an offset of the other.
- Marcus
Hi Sunny,
the vMaxEndDate is the variable that you helped with a few days ago, so its the date that we have data for all customers. it can change anytime - its not the year end.
what i mean by last year YTD is the same period last year, so this year would be..
Mon 03/07/2017 - Mon 09/10/2017
last year would be
Mon 04/07/2016 - Mon 10/10/2016
so last year is exactly the same period as this year, which then allows us to report like for like.
obviosuly then the variable will change as needed, so it could be theat next week we receive data for all customers up until 18/12/2017. that then means that the vMaxEndDate would be 18/12/2017 and the dates needed to compare this yr to last yr would be
Mon 03/07/2017 - Mon 18/12/2017
last year would be
Mon 04/07/2016 - Mon 19/12/2016
does that make sense?