4 Replies Latest reply on May 31, 2017 9:13 PM by yashdeep singh

# Week calculation in ascending order of year

Hi All,

I have a sample data (just for Practice) where the dates  starts and ends in mid year(Jul-14 to Jun-15). i converted the dates into weeks using formula but the issue is that week starts from Jan-15 instead of Jul-14 which is not correct.

Could anyone help me in correcting the same.

I am attaching the packaged workbook for your reference.

• ###### 1. Re: Week calculation in ascending order of year

hi Yashdeep,

Yes I can help you with this...but can you save your sample workbook as a .twbx (as a .twb I'm unable to access the data, a .twbx will zip the data and workbook together).

Also a quick question...do you want your week numbers to start at Jul-14 as this is where your data starts from?, or because your financial year runs from Jul-14 to June-15?...it'll just change how I approach it.

• ###### 2. Re: Week calculation in ascending order of year

Sorry for that i have now attached the twbx file. Secondly, there is no such financial year involved in it its just that the data starts from Jul-14.

• ###### 3. Re: Week calculation in ascending order of year

Thanks...

So I've created a Week Number formula, using an LoD (the FIXED LoD here just returns the 1st week in the data)

[Week Number (from 1st Week)]

(DATEDIFF('week',[Order Date],{MIN([Order Date])})*-1)+1

and the "How it works" tab shows you how it works. I've then used this the "Final_Simple" tab to show it in action.

Now it occurred to me that you have missing weeks!...and you might want this to be clear. So in order to create the "missing" weeks, we need to use a Range-aware pill (these are dates or bins...they are dimensions that know their Min, Max and Increment). So I created a bin from our new field if size 1

and then used this Bin instead...as a bin (range aware) we get this option

This is shown in the "Final_bit more complicated" tab.

Hope that all makes sense, and helps

1 of 1 people found this helpful
• ###### 4. Re: Week calculation in ascending order of year

Excellent Sir,

Many thanks for the detailed explanation & help. Much Appreciated. Hope will gain more knowledge from you in future as well.

Regards

Yashdeep Singh