Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to deduce fiscal year in custom calender?

I amtrying to create a fiscal calender that starts in October (starts on Saturday for the week whose Monday is first the Monday of October). So fiscal week 1 for 2012 starts actually on Sat, 1st Oct 2011 and goes on.

I am using below script to adjust the weeks and years.

right (WeekName(TempDate,13,-2),2) as FiscalWeek

if(Right(WeekName(TempDate,13,-2),2)>0 and Right(WeekName(TempDate,13,-2),2)<=13,YearName(TempDate,1),YearName(TempDate,0)) as FiscalYear,

Weeks are working perfect. However, I am facing problem in scripting years properly. To deduce fiscal year, I am incrementing calender year by 1 for first 13 fiscal weeks to deduce fiscal year and after that use calender year as fiscal year (Screenshot attached). This does not apply to certain exceptions like 12/31/2012 (attached screenshot and qvd) as its in fiscal week 14 and calender year is still 2011 not 2012.

My question is how to handle years when fiscal calender different than calender year? Attached is the qvd for your reference. In my login please look for dates like 12/31/2011, 12/20/2012/12/31/2012 to get to the problem.

Thanks for helping with this. I very much appreciate it.

Capture.JPG

1 Solution

Accepted Solutions
Employee
Employee

Re: How to deduce fiscal year in custom calender?

If you use a variable to define the beginning of the fiscal year, the formulae usually become easier to handle. In my blog post Fiscal Year, I have an offset in months. You can most likely modify these formulae for your situation.

HIC

2 Replies
Employee
Employee

Re: How to deduce fiscal year in custom calender?

If you use a variable to define the beginning of the fiscal year, the formulae usually become easier to handle. In my blog post Fiscal Year, I have an offset in months. You can most likely modify these formulae for your situation.

HIC

Not applicable

Re: How to deduce fiscal year in custom calender?

Many thanks Henric. This is it!

Community Browser