topic Re: Fiscal Month in QlikView

Fiscal Month

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

So lets take last week for example

Header 1	Header 2
28th March	Monday
29th March	Tuesday
30th March	Wednesday
31st March	Thursday
1st April	Friday
2nd April	Saturday
3rd April	Sunday

// Dates to validate

// Monthly Validation Calendar

Let vMonthlyStart = num(MonthStart(AddMonths(Today(), -13))); // 13 months back
Let vMonthlyEnd = num(MonthStart(AddMonths(Today(), -1))); // Last Month

[Monthly Calendar]:
LOAD distinct
Fiscal_Year_Month as [Fiscal Year Month],
text(FiscalYearPeriod) as [Fiscal Year Period]//,
FROM
[..\Shared\QVDs\T_Calendar.qvd]
(qvd)
where num(Date) >= $(vMonthlyStart)
and num(Date) <= $(vMonthlyEnd);

// Avaya Daily Calendar

Let vDailyStart = num(AddMonths(num(Today())-1, -3)); // 3 months back
Let vDailyEnd= num(Today())-1; // Yesterday

[Daily Calendar]:
LOAD Date,
Formatted_Date,
Year,
Month,
Week
FROM
[..\Shared\QVDs\T_Calendar.qvd]
(qvd)
where num(Date) >= $(vDailyStart)
and num(Date) <= $(vDailyEnd)
and weekday(Date)<5;

I want to show that the start of the Fiscal Year Month is the 28th of March (even though the start of the month is Friday but the Fiscal Month starts on the Monday.

How do I create a variable to reflect this?

Re: Fiscal Month

kaushiknsolanki — Mon, 04 Apr 2016 08:54:47 GMT

Hi,

You just want to flag the start of the fiscal Year Month as 28th March 2016?

Then you can do this.

Load *, If(Date = Yearstart(Date,0,4),Weekstart(Date)) as FiscalYearMonth

From xyz;

Regards,

Kaushik Solanki

Re: Fiscal Month

Gysbert_Wassenaar — Mon, 04 Apr 2016 08:56:26 GMT

The easy way out is to create your calendar in excel and load it from there.

Re: Fiscal Month

bobbydave — Mon, 04 Apr 2016 08:58:00 GMT

Yes, next month, the fiscal month end is 25th of April (as the Sunday is May 1st).

Re: Fiscal Month

kaushiknsolanki — Mon, 04 Apr 2016 09:00:27 GMT

If you want for all the months then try this.

Load *, If(Date = Monthstart(Date),Weekstart(Date)) as FiscalYearMonth

From xyz;

Regards,

Kaushik Solanki

Re: Fiscal Month

tyagishaila — Mon, 04 Apr 2016 09:34:20 GMT

Agree with Gysbert.