topic Plotting Flags for all CYTD and FYTD Dates in QlikView

Plotting Flags for all CYTD and FYTD Dates

rohitians — Wed, 18 Jul 2018 09:39:06 GMT

Hi Friends,

Let vMinDate=Num(Date('01/01/2013','DD/MM/YYYY'));

Let vMaxDate=Num(Date('17/07/2018','DD/MM/YYYY'));

Let vFiscalMoShift = 2; //Configure in Reference File

let vFirstMonthOfFiscalYear=11; //Configure in Reference File

tmpCalendar:

Load

RowNo(),

Date($(vMinDate) + RowNo() - 1) as TempDate

Autogenerate

$(vMaxDate) - $(vMinDate) + 1;

MasterCalendarTemp:

Load

Date(Floor(TempDate)) as Date,

Resident tmpCalendar

Order By TempDate ASC;

Drop table tmpCalendar;

Flags:

Generic Load Date , 'FY_' & Year , 1

Resident MasterCalendarTemp;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Flags.*') THEN

LEFT JOIN (MasterCalendarTemp) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i

MasterCalendar:

Load *,

Year(Date) As CalendarYear

Resident MasterCalendarTemp

Order By Date ASC;

Drop table MasterCalendarTemp;

Flags:

Generic Load Date , 'CY_' & CalendarYear , 1

Resident MasterCalendar;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Flags.*') THEN

LEFT JOIN (MasterCalendar) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i

On the same lines I want to create FYTD and CYTD Flags for all the Years

Cuutently it is 18-07-2018

So i want flag as 1 until

18-07-2018

18-07-2017

18-07-2016 and so on..

Please help.

Thanks,

Rohit Yadav

Re: Plotting Flags for all CYTD and FYTD Dates

sunny_talwar — Wed, 18 Jul 2018 10:25:19 GMT

Not sure I understand? Do you wish to flag 1 to all dates July 18th from all the years in your dashboard?

Re: Plotting Flags for all CYTD and FYTD Dates

rohitians — Wed, 18 Jul 2018 10:58:10 GMT

Hi Sunny,

Yes for example,

Fiscal Year is 1-11-2017 to 31-10-2018

and Calender year would be 1-01-2018 to 31-12-2018

So flag CYTD_2018 will have 1 from 1-01-2018 to 18-07-2018 and

FYTD_2018 Flag will have 1 from 1-11-2017 to 18-07-2018.

This would be done for all previous Years like,

So flag CYTD_201 will have 1 from 1-01-2017 to 18-07-2017 and

FYTD_2017 Flag will have 1 from 1-11-2016 to 18-07-2017.

Or else please suggest any suitable solution,

reason for using generic load was the flags are dynamic I don't need to manually add a new year.

Thanks,

Rohit Yadav

Re: Plotting Flags for all CYTD and FYTD Dates

sunny_talwar — Wed, 18 Jul 2018 11:03:26 GMT

I have not done a lot of testing with this... but see if this blog helps you in any way

Period Presets: Compare Periods on the fly

Re: Plotting Flags for all CYTD and FYTD Dates

rohitians — Wed, 18 Jul 2018 11:23:40 GMT

Hi Sunny,

Thanks for suggesting but Flags are for business users

who could use it directly for self serve.

I have to keep it simple like i did it for FY and CY .

Thanks,

Rohit Yadav

Re: Plotting Flags for all CYTD and FYTD Dates

rohitians — Thu, 19 Jul 2018 08:42:46 GMT

Hi Friends,

Following is the solution.

Let vMinDate=Num(Date('01/01/2013','DD/MM/YYYY'));

Let vMaxDate=Num(Date('17/07/2018','DD/MM/YYYY'));

Let vFiscalMoShift = 2; //Configure in Reference File

let vFirstMonthOfFiscalYear=11; //Configure in Reference File

tmpCalendar:

Load

RowNo(),

Date($(vMinDate) + RowNo() - 1) as TempDate

Autogenerate

$(vMaxDate) - $(vMinDate) + 1;

MasterCalendarTemp:

Load

Date(Floor(TempDate)) as Date,

Year(Yearstart(TempDate, 0, 11)) + 1 As Year,

if(Date(Floor(TempDate))<=Today() and Date(Floor(TempDate))>= Yearstart(Today(), 0, 11)

,Year(Yearstart(Today(), 0, 11)) + 1 ,

if(Date(Floor(TempDate))<=addYears(Today(),-1) and Date(Floor(TempDate))>= Yearstart(addYears(Today(),-1), 0, 11)

,Year(Yearstart(addYears(Today(),-1), 0, 11)) +1 ,

if(Date(Floor(TempDate))<=addYears(Today(),-2) and Date(Floor(TempDate))>= Yearstart(addYears(Today(),-2), 0, 11)

,Year(Yearstart(addYears(Today(),-2), 0, 11)) + 1,

if(Date(Floor(TempDate))<=addYears(Today(),-3) and Date(Floor(TempDate))>= Yearstart(addYears(Today(),-3), 0, 11)

,Year(Yearstart(addYears(Today(),-3), 0, 11)) + 1,

if(Date(Floor(TempDate))<=addYears(Today(),-4) and Date(Floor(TempDate))>= Yearstart(addYears(Today(),-4), 0, 11)

,Year(Yearstart(addYears(Today(),-4), 0, 11)) + 1,

if(Date(Floor(TempDate))<=addYears(Today(),-5) and Date(Floor(TempDate))>= Yearstart(addYears(Today(),-5), 0, 11)

,Year(Yearstart(addYears(Today(),-5), 0, 11)) + 1

)))))) As FYTDYear,

if(Date(Floor(TempDate))<=Today() and Date(Floor(TempDate))>= Yearstart(Today())

,Year(Yearstart(Today())) ,

if(Date(Floor(TempDate))<=addYears(Today(),-1) and Date(Floor(TempDate))>= Yearstart(addYears(Today(),-1))

,Year(Yearstart(addYears(Today(),-1))) ,

if(Date(Floor(TempDate))<=addYears(Today(),-2) and Date(Floor(TempDate))>= Yearstart(addYears(Today(),-2))

,Year(Yearstart(addYears(Today(),-2))) ,

if(Date(Floor(TempDate))<=addYears(Today(),-3) and Date(Floor(TempDate))>= Yearstart(addYears(Today(),-3))

,Year(Yearstart(addYears(Today(),-3))),

if(Date(Floor(TempDate))<=addYears(Today(),-4) and Date(Floor(TempDate))>= Yearstart(addYears(Today(),-4))

,Year(Yearstart(addYears(Today(),-4))) ,

if(Date(Floor(TempDate))<=addYears(Today(),-5) and Date(Floor(TempDate))>= Yearstart(addYears(Today(),-5))

,Year(Yearstart(addYears(Today(),-5)))

)))))) As CYTDYear

Resident tmpCalendar

Order By TempDate ASC;

Drop table tmpCalendar;

Flags:

Generic Load Date , 'FY_' & Year , 1

Resident MasterCalendarTemp;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Flags.*') THEN

LEFT JOIN (MasterCalendarTemp) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i

MasterCalendar:

Load *,

Year(Date) As CalendarYear

Resident MasterCalendarTemp

Order By Date ASC;

Drop table MasterCalendarTemp;

Flags:

Generic Load Date , 'CY_' & CalendarYear , 1

Resident MasterCalendar;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Flags.*') THEN

LEFT JOIN (MasterCalendar) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i

Flags:

Generic Load Date , 'FYTD_' & FYTDYear , 1

Resident MasterCalendar;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Flags.*') THEN

LEFT JOIN (MasterCalendar) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i

Flags:

Generic Load Date , 'CYTD_' & CYTDYear , 1

Resident MasterCalendar ;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Flags.*') THEN

LEFT JOIN (MasterCalendar) LOAD * RESIDENT [$(vTable)];

DROP TABLE [$(vTable)];

ENDIF

NEXT i