Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Partner
Partner

Re: Help creating a new field in calendar

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

Re: Help creating a new field in calendar

Make sense, look at it now...

Re: Help creating a new field in calendar

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;

Partner
Partner

Re: Help creating a new field in calendar

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?

Re: Help creating a new field in calendar

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

Partner
Partner

Re: Help creating a new field in calendar

Thanks so much Sunny. that works!

i wont bother you again (this week )

have a nice xmas all!