Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

qlikmeplease
Contributor III

FinYearWeek Calculations

Hi All,

How can you calculate the Financial Year and Week number from a date.

For example:

All information I have is a date column which contains dates in the format 'YYYY/MM/DD'.

The requirement is that I get the Financial Year and the week number as a code.

Week(Date) provides the week of the year.

Year(Addmonths(Date),12) will provide me with the financial year.

However, using the below does not yield the correct result:

Year(Addmonths(DayDate,12)) & num(week(addmonths(DayDate,6)),'00')

My Financial Year starts in July of the year.

Qlikview starts the week on a Saturday to Friday period, and that is not correct, my weeks need to start on a monday and finish on a sunday.

My variables are set up as below:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

4 Replies

Re: FinYearWeek Calculations

Can you set in your variable environment like

SET FirstWeekDay=2; // 1 Starts from Sunday.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: FinYearWeek Calculations

Hi Ruan,

Have a look at the blogs below by Henric Cronstrom hic which detail how to setup calendars for Financial Year & Weeks starting at different months.

Redefining the Week Start

Redefining the Week Numbers

Re: FinYearWeek Calculations

Are you doing this in script if so, this works as From Date we need to go back 180 Days that means we need to calculate how many dates week has. So, 180 / 7 = 25. If Today() it returns 39 - 25 = 13. And output returns here 2017/13 so from this we are picking Right side of 2 strings and it should return 13 as always..

HTH

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
qlikmeplease
Contributor III

Re: FinYearWeek Calculations

Solved this by using the below formula,

For any future enquiries, bare in mind that our financial year period starts in july.  And we work on a Mon-Sun Period.  The formula should just be adjusted accordingly.

Year(Addmonths(Date(DayDate,'YYYY/MM/DD'),12)) & num(if(Week(DayDate)+26 > 52,Week(DayDate)-26,Week(DayDate)+26),'00')