topic Re: Fiscal week number starting from monday in App Development

Fiscal week number starting from monday

Sagar_Apte — Wed, 16 Sep 2020 15:29:50 GMT

Hi All,

I need to create week number for fiscal calendar goes from Apr to Mar. Also week number should start from monday.

For eg. In FY 15-16, my first week should start from 07/Apr/15 as first monday of this fiscal year starts from this day. Similarly it should apply for other years as well.

Note: When I have partial financial year data week number should not start with 1, it should start with actual week number for that fiscal year although it has partial data.

Currently I tried below logic which gives me week number from start of the financial year i.e 01/Apr which I don't want. I know there are lot many resources for this but could not find anything related to what I want.

Div(Date-YearStart(Date,0,4),7)+1 as FiscalWeek

@sunny_talwar @Kushal_Chawda @Vegar

Re: Fiscal week number starting from monday

sunny_talwar — Wed, 16 Sep 2020 15:50:08 GMT

Would you be able to provide sample data and the output you expect to see from it?

Re: Fiscal week number starting from monday

Vegar — Wed, 16 Sep 2020 18:44:37 GMT

I'm not sure if I understood all the details in your specification, but you could probably get to your correct week number using week() to some extent.

Maybe like this: week(AddMonths([Date],-3) )

Re: Fiscal week number starting from monday

Sagar_Apte — Wed, 16 Sep 2020 22:24:44 GMT

@Vegar @sunny_talwar let's say I have fiscal year 14/15(Apr 2014 to Mar 2015). Now I need week number like below

Date WeekNumber

1 Apr 2014 - 6 Apr 2014 1 (as week starts from monday only 6 days will go to 1st week as 7 is monday)

7 Apr 2014-13 Apr 2014 2

14 Apr 2014 -20 Apr 2014 3

.......

30 Mar 2015 -31 Mar 2015 53 (only two days goes to this week as financial year is ending)

Similarly logic should go for all financial

I have partial data for fiscal year FY 13/14. i.e 1 Apr 2014 to 31 Mar 2014. In this case wek number should go like below

1 Apr 2014-5 Apr 2014 40 ( as these are part of week 40 of year FY 13/14)

6 Apr 2014-12 Apr 2014 41

.....

31 Mar 2014 53 (only 1 day goes to this week as it is year end)

I hope I am clear now.

Re: Fiscal week number starting from monday

Kushal_Chawda — Wed, 16 Sep 2020 22:55:20 GMT

@Sagar_Apte try below in your calendar script or in table load. Assuming your Date field is properly formatted

Ceil((WeekStart(Date,0,0)-YearStart(Date,0,4))/7)+1 as WeekNumber;

Re: Fiscal week number starting from monday

Sagar_Apte — Wed, 16 Sep 2020 23:07:13 GMT

@Vegar it is not working as expected. For eg. for FY 14/15, It's giving week number 1 only for Date 1 Apr 2014 to 4 Apr 2014..whereas I need week number as 1 for 1 Apr 2014 to 6 Apr 2014 as week should start from monday..so from 7 Apr 2014 week 2 will start

Re: Fiscal week number starting from monday

Sagar_Apte — Thu, 17 Sep 2020 10:08:05 GMT

@Kushal_Chawda thanks a lot. working as expected