topic Re: Need to calculate first Monday in February for fiscal calendar in App Development

Need to calculate first Monday in February for fiscal calendar

markgraham123 — Tue, 05 Jan 2016 19:34:32 GMT

Hello Everyone,

I'm trying to create a master calendar with fiscal weeks and year.

My fiscal week should start from First Monday of February as Fiscal Week 1.

So, i wanna automate something which should not change with respect to year.

Any help is highly appreciated!

I tried the following, but no luck. It changes year by year.

if(week(TempDate) > 5,week(TempDate)-5, week(TempDate) + 47) As [Fiscal Week],

if((week(TempDate) < 5), (year(TempDate)-1), year(TempDate)) as [Fiscal Year],

Re: Need to calculate first Monday in February for fiscal calendar

sunny_talwar — Tue, 05 Jan 2016 19:38:14 GMT

Try may be this:

WeekStart(MonthStart(TempDate) + 6, 0, 0) -> to find first Monday for each month

Re: Need to calculate first Monday in February for fiscal calendar

markgraham123 — Tue, 05 Jan 2016 19:41:56 GMT

Sunny,

Can't we fix the master calendar..saying that, from the first monday of february till last week of next january....

it will be the Fiscal year(Today)....??

Re: Need to calculate first Monday in February for fiscal calendar

sunny_talwar — Tue, 05 Jan 2016 19:48:42 GMT

This?

Table:

LOAD *,

'FY' & Year([First Monday]) as [Fiscal Year];

LOAD Date,

WeekStart(YearStart(Date, 0, 2) + 6, 0, 0) as [First Monday],

MonthName(Date) as MonthYear;

LOAD Date(MakeDate(2013,12,31) + RecNo()) as Date

AutoGenerate 1095;

Re: Need to calculate first Monday in February for fiscal calendar

markgraham123 — Tue, 05 Jan 2016 20:04:20 GMT

Sunny,

It's working for 2015 and 2016.

But when i checked for 2017...

it is showing fiscal year as 2017 from 2/1/2017 !!

It should show fiscal year 2016 till 2/6/2017 right???

From 2/7/2017, it should show the fiscal year 2017.

Re: Need to calculate first Monday in February for fiscal calendar

sunny_talwar — Tue, 05 Jan 2016 20:10:55 GMT

Got it. Try this:

Table:

LOAD *,

'FY' & If(Month(Date) = 'Feb' and Day(Date) <= Day([First Monday]), Year([First Monday]) - 1, Year([First Monday])) as [Fiscal Year];

LOAD Date,

WeekStart(MonthStart(YearStart(Date, 0, 2)) + 6, 0, 0) as [First Monday],

MonthName(Date) as MonthYear;

LOAD Date(MakeDate(2013,12,31) + RecNo()) as Date

AutoGenerate 1460;

Re: Need to calculate first Monday in February for fiscal calendar

markgraham123 — Tue, 05 Jan 2016 20:37:09 GMT

Sunny, as of my requirement, i just removed the less = symbol in the condition which is calculaitng the fiscal year and it worked

My actual problem is, when we calculate the fiscal week,

fiscal week changes for every year's week.

Can we make the script so as to consider the fiscal week as 1, when we encounter the first monday in feb.??

Re: Need to calculate first Monday in February for fiscal calendar

markgraham123 — Tue, 05 Jan 2016 21:09:12 GMT

I'm using this:

if(week(TempDate)< Week(WeekStart(MonthStart(YearStart(TempDate, 0, 2)) + 6, 0, 0)), week(TempDate) + 47, week(TempDate)-5) as Fiscal week

to make a fiscal week start from the first monday of every february.

But, since i'm givign 5 in the condition, it is giving fiscal week '0' for few years

Re: Need to calculate first Monday in February for fiscal calendar

sunny_talwar — Tue, 05 Jan 2016 21:16:19 GMT

May be this:

Table:

LOAD *,

If(Week(Date, 0)< Week([First Monday]), Week(Date, 0) + 47, Week(Date, 0)-5) as [Fiscal Week],

'FY' & If(Month(Date) = 'Feb' and Day(Date) < Day([First Monday]), Year([First Monday]) - 1, Year([First Monday])) as [Fiscal Year];

LOAD Date,

WeekStart(MonthStart(YearStart(Date, 0, 2)) + 6, 0, 0) as [First Monday],

MonthName(Date) as MonthYear;

LOAD Date(MakeDate(2013,12,31) + RecNo()) as Date

AutoGenerate 1460;

Re: Need to calculate first Monday in February for fiscal calendar

markgraham123 — Tue, 05 Jan 2016 21:34:58 GMT

Still same error bro.

Showing 2016 feb 1st week as

fiscal week 0

Re: Need to calculate first Monday in February for fiscal calendar

sunny_talwar — Tue, 05 Jan 2016 21:55:45 GMT

Give this a try:

Table:

LOAD *,

If(Week(Date, 0)< Week([First Monday]), Week(Date, 0) + 47, Week(Date, 0) - 5) as [Fiscal Week],

Week(AddMonths(Date, -1) - Day([First Monday]) + 1) as [Fiscal Week1],

'FY' & If(Month(Date) = 'Feb' and Day(Date) < Day([First Monday]), Year([First Monday]) - 1, Year([First Monday])) as [Fiscal Year];

LOAD Date,

WeekStart(MonthStart(YearStart(Date, 0, 2)) + 6, 0, 0) as [First Monday],

MonthName(Date) as MonthYear;

LOAD Date(MakeDate(2013,12,31) + RecNo()) as Date

AutoGenerate 1460;