Fiscal calendar

[satishqlik]

am trying to create fiscal calendar but am not getting accurate results.

Please have a look at my sample qvw file and data.

and please suggest where I did wrong.

Regards,

SAtish

[sunny_talwar]

check attached

[neelamsaroha157]

I think its the date format. You have MM/DD/YYYY & DD/MM/YYYY.

Try to have same format across the application.

[neelamsaroha157]

Sunny was faster .

[satishqlik]

Thanks sunny for your reply

But am unable to open your attached file as am using personal edition.

Can you paste your script here??

[sunny_talwar]

I just posted the script... why did it removed?

[satishqlik]

Hey I didn't remove your script

Please check it once.

Apologies!! If I did any thing wrong.

[sunny_talwar · Accepted solution]

Here you go... changes in red

temp:

LOAD empid,

     date,

     plant,

     cat,

     status

FROM

fiscal_doubt.xlsx

(ooxml, embedded labels, table is Sheet1);

SET vFiscalYearStartMonth = 4;

DateRange:

LOAD Min(date) AS MinDate,

     Max(date) AS MaxDate

Resident temp;

LET vStartDate = Peek('MinDate');

LET vEndDate = Peek('MaxDate');

DROP TABLE DateRange;

FiscalCalendar:

LOAD *,

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

     Dual(Text(Date(MonthEnd(date), 'MMM')), FiscalMonth) AS FiscalMonthName;

LOAD *,

     Year(date) AS Year,

     Month(date) AS Month,

     Date(MonthEnd(date), 'MMM') AS MonthName,

     Dual('Q' & Ceil(Month(date)/3), Ceil(Month(date)/3)) AS Quarter,

     Mod(Month(date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, 

     YearName(date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; 

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

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

AutoGenerate vEndDate - vStartDate + 1;