topic Re: FinYearWeek Calculations in QlikView

FinYearWeek Calculations

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: FinYearWeek Calculations

Anil_Babu_Samineni — Tue, 26 Sep 2017 11:54:32 GMT

Can you set in your variable environment like

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

Re: FinYearWeek Calculations

Colin-Albert — Tue, 26 Sep 2017 11:56:21 GMT

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

Anil_Babu_Samineni — Tue, 26 Sep 2017 13:15:14 GMT

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

Re: FinYearWeek Calculations

Anonymous — Tue, 26 Sep 2017 13:29:11 GMT

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