FinYearWeek Calculations

[Anonymous]

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';

[Anil_Babu_Samineni]

Can you set in your variable environment like

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

[Colin-Albert]

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

[Anil_Babu_Samineni]

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

[Anonymous]

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')