Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bobbydave
Creator III
Creator III

Fiscal Month

So lets take last week for example

Header 1Header 2
28th MarchMonday
29th MarchTuesday
30th MarchWednesday
31st MarchThursday
1st AprilFriday
2nd AprilSaturday
3rd AprilSunday

// 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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
bobbydave
Creator III
Creator III
Author

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
tyagishaila
Specialist
Specialist

Agree with Gysbert.