topic Re: Fiscal calendar in QlikView

Fiscal calendar

satishqlik — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Fiscal calendar

sunny_talwar — Tue, 09 May 2017 14:07:57 GMT

check attached

Re: Fiscal calendar

neelamsaroha157 — Tue, 09 May 2017 14:11:07 GMT

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

Try to have same format across the application.

Re: Fiscal calendar

neelamsaroha157 — Tue, 09 May 2017 14:12:14 GMT

Sunny was faster .

Re: Fiscal calendar

satishqlik — Tue, 09 May 2017 16:58:30 GMT

Thanks sunny for your reply

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

Can you paste your script here??

Re: Fiscal calendar

sunny_talwar — Tue, 09 May 2017 16:59:38 GMT

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

Re: Fiscal calendar

satishqlik — Tue, 09 May 2017 17:02:47 GMT

Hey I didn't remove your script

Please check it once.

Apologies!! If I did any thing wrong.

Re: Fiscal calendar

sunny_talwar — Tue, 09 May 2017 17:10:50 GMT

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;