topic Re: Fiscal cal in QlikView

Fiscal cal

nareshthavidishetty — Wed, 24 Feb 2016 10:03:16 GMT

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...

Re: Fiscal cal

sunny_talwar — Wed, 24 Feb 2016 10:06:17 GMT

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

Re: Fiscal cal

Anonymous — Wed, 24 Feb 2016 10:07:46 GMT

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

Re: Fiscal cal

nareshthavidishetty — Wed, 24 Feb 2016 10:57:35 GMT

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

Re: Fiscal cal

sunny_talwar — Wed, 24 Feb 2016 11:30:29 GMT

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;

Re: Fiscal cal

nareshthavidishetty — Wed, 24 Feb 2016 12:18:34 GMT

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;

Re: Fiscal cal

sunny_talwar — Wed, 24 Feb 2016 12:19:47 GMT

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?

Re: Fiscal cal

nareshthavidishetty — Wed, 24 Feb 2016 12:21:31 GMT

Below is the error am getting,

Re: Fiscal cal

sunny_talwar — Wed, 24 Feb 2016 12:25:08 GMT

Can you share the script?

Re: Fiscal cal

nareshthavidishetty — Wed, 24 Feb 2016 12:34:32 GMT

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,

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;

Re: Fiscal cal

sunny_talwar — Wed, 24 Feb 2016 12:38:13 GMT

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,

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;

Re: Fiscal cal

nareshthavidishetty — Wed, 24 Feb 2016 12:48:47 GMT

It's working .

Thanks

Re: Fiscal cal

jagan — Fri, 26 Feb 2016 01:33:22 GMT

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.