topic Re: Aligning dates to Fiscal Year in QlikView

Aligning dates to Fiscal Year

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

Hi,

I have the following in the script to align the dates to the Fiscal Year July - June.

if(Month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) > 6, Year(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))+1, Year(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)))) as FiscalYear,

dual(month(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))),month(addmonths(Date([SAL_InvoiceDate] + MakeDate(2000,1,1)),6))) as FiscalMonth,

Week(Date([SAL_InvoiceDate] + MakeDate(2000,1,1))) as Week;

Currently, the above expression for the week shows the week is aligned to the calendar year. I need an additional expression for the week to be aligned to the fiscal year.

Any ideas?

Thanks

Shamit

Re: Aligning dates to Fiscal Year

sunny_talwar — Tue, 07 Jun 2016 00:09:59 GMT

Have you tried playing around with the scrip provided by jagan‌

Fiscal and Standard Calendar generation

Re: Aligning dates to Fiscal Year

shamitshah — Tue, 07 Jun 2016 03:48:40 GMT

The script is working fine as far as the months and quarters are concerned.

I wanted to bring in the weeks and align them to the fiscal year i.e. July - June

I included the following,but still having issues in that the week start's on Monday as opposed to Sunday:

Set vCal_FD = 6; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

WeekStart(Date ([PKL_PickedDate]+ MakeDate(2000,1,1)), 6, $(vCal_FD))as WeekStart,

dual(WeekStart(Date([PKL_PickedDate] + MakeDate(2000,1,1))),WeekStart(AddMonths(Date([PKL_PickedDate] + MakeDate(2000,1,1)),6))) as PickedFiscalWeek;

Thanks

Shamit