Fiscal cal

[nareshthavidishetty]

Hi,

Below is the script used to get quarter and fiscal month

Mod(If(Len(Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY'))))<2,0 & Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY'))),Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY')))) - $(vFM), 12)+1 as fMonth,

    'Q' & Ceil((Mod(If(Len(Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY'))))<2,0 & Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY'))),Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY')))) - $(vFM), 12)+1)/3) as Quarter,

it gives as below

Here we need to show like my month should start with Feb and ends with Jan and also if the user select year=2016 then chart gives data from Feb,...means the fisical year starts from 2015 Feb to 2016, Jan2016 Feb to 2017 Jan...

[sunny_talwar · Accepted solution]

Try this:

APP:

LOAD

date((FCFSDT),'MM/D/YYYY') as SUB_Date,

      FCFSDT,

      COMP,

      FCAACD as Location,                                                              

    APPL# as ApplicationNbr,                                                          

      FCALNB as CustomerID,

      [FCK0ST],                                                    

    pick(match([FCK1ST],'R','W'),'In-store','Offsite Site (Web+Ph+Mail)') as Channel,

    pick(match([FCK0ST],'A','C','D','I','Q','U','W'),'Approved','Canceled','Declined','In Store','queued','Used','Waiting') as ApplicationStatus,

  pick(match([COMP],'C3D6D5','C7C5D4'),'Conns Application','GE Application') as ApplicationCategory,

  // if(APPL# >9999, 'Conns Application' , 'GE Application' ) as Status,                                                          

  Month(Date(AddYears(Date(Date#('19' & Right(FCFSDT,6),'YYYYMMDD'), 'DD/MM/YYYY'), 100 * Left(FCFSDT,1)), 'M/D/YYYY')) as Month,

  Year(Date(AddYears(Date(Date#('19' & Right(FCFSDT,6),'YYYYMMDD'), 'DD/MM/YYYY'), 100 * Left(FCFSDT,1)), 'M/D/YYYY')) as Year,

  Week( Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'DD/MM/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY')) as Week,

  Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY') as Application_Date,

  'W' & Ceil(Day(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY'))/7) AS WeekInMonth,

  Mod(If(Len(Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY'))))<2,0 & Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY'))),Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY')))) - $(vFM), 12)+1 as fMonth,

  'Q' & Ceil((Mod(If(Len(Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY'))))<2,0 & Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY'))),Num(Month(Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'MM/DD/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY')))) - $(vFM), 12)+1)/3) as Quarter,

  Day( Date(AddYears(Date(Date#('19'&Right(FCFSDT,6),'YYYYMMDD'),'DD/MM/YYYY'),100*Left(FCFSDT,1)),'M/D/YYYY')) as ApplicationDate;

SQL SELECT *

FROM AS400D60.SPCTDBF.APPLOCCNT

where FCFSDT  >= $(vMonLY);

MinMax:

LOAD Min(Application_Date) as MinDate,

  Max(Application_Date) as MaxDate

Resident APP;

SET vFiscalYearStartMonth = 2;

LET vStartDate = Peek('MinDate');

LET vEndDate = Peek('MaxDate');

DROP Table MinMax;

FiscalCalendar:

LOAD *,

  Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

  Dual(Text(Date(MonthEnd(Application_Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD *,

  Year(Application_Date) AS Year, // Standard Calendar Year

  Month(Application_Date) AS Month, // Standard Calendar Month

  Date(MonthEnd(Application_Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

  Dual('Q' & Ceil(Month(Application_Date)/3), Ceil(Month(Application_Date)/3)) AS Quarter,  // Standard Calendar Quarter

  Mod(Month(Application_Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

  YearName(Application_Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year

LOAD Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Application_Date,

  RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

[nareshthavidishetty]

It's working .

Thanks

[jagan]

HI,

Replace Application_Date with Datee it will work.

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Datee,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

Or change Datee with Application_Date.

Hope this helps you.

Regards,

Jagan.