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.

1 Solution

Accepted Solutions
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;

View solution in original post

15 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

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;

sunny_talwar

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);

marcus_sommer

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

luismadriz
Specialist
Specialist

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,

sunny_talwar

You are right, if -1 is needed, just need the change the order of subtraction

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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?

sunny_talwar

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?

marcus_sommer

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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?