Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmeplease
Creator III
Creator 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
Anil_Babu_Samineni

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

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)
qlikmeplease
Creator III
Creator III
Author

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