Fiscal Month

[bobbydave]

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?

[kaushiknsolanki]

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

[Gysbert_Wassenaar · Accepted solution]

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

[bobbydave]

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

[kaushiknsolanki]

If you want for all the months then try this.

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

From xyz;

Regards,

Kaushik Solanki

[tyagishaila]

Agree with Gysbert.