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]

Why don't you use a fiscal year calendar. jagan‌ has provided the complete code here: Fiscal and Standard Calendar generation

Only thing you need to change here is this -> SET vFiscalYearStartMonth = 4; Change the 4 to 2 to make Feb you 1st month

UPDATE: Here is a sample attached

[Anonymous]

Ctrl+E -> Go to Main Tab of Set default variables -> Change the order like this

SET MonthNames='Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec;Jan';

For Fiscal Year, you can try like this?

YearName(DateField,0,2) as Fiscal Year      //2 for Feb

[nareshthavidishetty]

Hi sunny,

Below is the script error ,

SET vFiscalYearStartMonth = 2;

LET vStartDate = Num(YearStart(Today(), -1));

LET vEndDate = Num(YearEnd(Today()));

Join(APP)

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(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Application_Date)/3), Ceil(Month(Application_Date)/3)) AS Quarters,  // 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 Datee,

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

AutoGenerate vEndDate - vStartDate + 1;

But am getting an error as below

[sunny_talwar]

Try this:

FactTable:

LOAD * Inline [

Application_Date

1/12/2014

6/12/2015

];

MinMax:

LOAD Min(Application_Date) as MinDate,

  Max(Application_Date) as MaxDate

Resident FactTable;

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(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD *,

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

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

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

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

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

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

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

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

AutoGenerate vEndDate - vStartDate + 1;

[nareshthavidishetty]

Hi,

Am getting an error Application_Date not found but am using the same field in APP table,

below is the script,

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(Date)/3), Ceil(Month(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 Date,

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

AutoGenerate vEndDate - vStartDate + 1;

[sunny_talwar]

Are you sure you have a field name Application_Date in a table named APP? Are you using Qualify statement somewhere in your script before you load APP table?

[nareshthavidishetty]

Below is the error am getting,

[sunny_talwar]

Can you share the script?

[nareshthavidishetty]

Hi,

Below is the script,

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(Date)/3), Ceil(Month(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 Date,

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

AutoGenerate vEndDate - vStartDate + 1;

//Resident APP;