Start and End Date

[former_member402770]

Hi Experts,

I need to display current fiscal period based on below derivation from the last execution date of the report compared with below:

The client calendar is 4 3 5 fiscal week based..

Step which needs your help

1) Start Date of every fiscal year, say  according to the system date it falls in thursday below is the snapshot.

so based on the mentioned calendar 435 fiscal week base, my first number

4 fiscal weeks- > Start 1st Month of any fiscal year here 1st 2015 and my end date week by referring above is 24.

3 fiscal weeks ->  for second Month starts on 25th Jan and ends by 14th feb 2015

5 fiscal weeks -> fiscal week starts 15th Feb 2015  and ends by 21st mar 2015

...for remaining 435 base we are have to derive based on webi formula for below ones:

fiscal week of every year like 435 base in every quarter

start date/day of fiscal week in that year

end date/day of fiscal week in that year.

Can we able to achieve this arrive the start and end date of every month based out of fiscal week

base 435 and compare the report last execution date with our derived start and end date dates to get the current fiscal periods.

Thanks,

-Dinya.

[sateesh_kumar1 · Accepted solution]

Hi Dinya ,

Try below

V_Starting Date for Fisc Year

=ToDate("1/1/2015";"M/d/yyyy")

V_Month 1 Days                   

=27-(If(DayNumberOfWeek([V_Starting Date for Fisc Year])=6;0;Mod(DayNumberOfWeek([V_Starting Date for Fisc Year]);7)))

V_Month1 End date=RelativeDate([V_Starting Date for Fisc Year];[V_Month 1 Days])

V_Month2 Ending Date

=RelativeDate([V_Month1 End date];21)

V_Month3 Ending Date

=RelativeDate([V_Month2 Ending Date];35)

[former_member402770 · Accepted solution]

Hi Sateesh,

  Sorry for the late reply, works like charm..

Will this formula variable works for any fiscal year i have tested and working great for 2014 and 2015.

But for 2016, have an differential calendar like below

Jan = 30

Feb = 27

Not sure the future calendars are correct, but shall check this..

Kindly clarify your below formula variable as iam not understanding with an sample example:

V_Month 1 Days                   

=27-(If(DayNumberOfWeek([V_Starting Date for Fisc Year])=6;0;Mod(DayNumberOfWeek([V_Starting Date for Fisc Year]);7)))

Once again thanks for your great help.

Thanks,

Dinya

[sateesh_kumar1 · Accepted solution]

Hi Dinya ,

Glad to know it helps you .

Variable Explanation :

27 is for 4 (weeks in first period )*7 =28 -1 ,because the starting date will be considered as 0 .

DayNumberOfWeek([V_Starting Date for Fisc Year])=6

to know the date is Saturday or not .if it is Sunday we need to consider the weekday as 1 .

so that applied mod function to get 1 .

[former_member402770 · Accepted solution]

Hi Sateesh,

  Thanks it was understandable now, but I have an query here, correct me if iam wrong,

For example, if the date is Sunday, it returns 1; if the date is Friday, it returns 6

Saturday, it returns 7 means the below formula should still holds good?

DayNumberOfWeek([V_Starting Date for Fisc Year])=6 ?

Thanks,

-Dinya

[sateesh_kumar1 · Accepted solution]

Hi Din ,

we need to change the logic accordingly.Hope in BO week number of date is not going to change.

[former_member402770 · Accepted solution]

Hi Sateesh,

  We need to change the logic:

  Can you brief/elaborate this how and for why? and why this BO week number of date is not going to change.

if no, Was your above formula results the same.

Thanks,

-Dinya.