Plotting Flags for all CYTD and FYTD Dates

[rohitians]

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

[sunny_talwar]

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

[rohitians]

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

[sunny_talwar]

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

[rohitians]

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

[rohitians · Accepted solution]

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