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: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Help creating a new field in calendar

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.

15 Replies
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

also, the calc needed to work out the vMaxEndDate for previous years can be ignored, i have just realised that i have variables for these that i can re use.

so its just getting help defining the start dates for each year.

so you have helped getting the flags set against each year

0 = current

-1 = last year

-2 = previosu yeat

etc

what i need is to get an if statement written to pick up all dates >= the date that has the flag of 0 <= vMaxEndDate and set this flag as 0.

e.g.

If(Date>=date that has flag of 0 and Date<='$(vMaxEndDate)',0,

     If(Date>=date that has flag of -1 and Date<='$(vMaxEndDateLY)',-1,


etc


im unsure how to get the 'date that has flag of 0' part

sunny_talwar

Make sense, look at it now...

sunny_talwar

May be 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 Day(CALENDAR_DDMMYYYY) >= Day(YearStartDateMonday) and CALENDAR_DDMMYYYY <= MinDate, Year(YearStart(CALENDAR_DDMMYYYY, 0, 7)) - Year(YearStart(Today(), 0, 7))) as Flag

Resident Dates;


DROP Table Dates;

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi Sunny,

thanks for this.

i see a problem though.

when selecting fiscal year 2018, i should have 0 against all dates from 03/07/2017-09/10/2017

but i am not getting a 0 on any of the 01 or 02 of each month. so its only flagging from 03 onwards.

its the same for the rest of the years too.

Capture.JPG

is this easily fixed?

sunny_talwar

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;

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Thanks so much Sunny. that works!

i wont bother you again (this week )

have a nice xmas all!