topic Re: Fiscal Calendar MonthStart in App Development

Fiscal Calendar MonthStart

johngouws — Wed, 04 May 2022 02:49:54 GMT

Good morning.

I am trying to define a Fiscal Calendar and have one field I cannot get to work correctly. That is the Fisc_MonthStart for January of the year. In the attached jpg file the field is empty but it should read ‘2021-12-26’.

My fiscal year is from 26^th February to the following year 25^th February.

Fisc_MonthStart is defined as below.

if(month(FiscalDate) > 2,

MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))), Num(Month(FiscalDate)-1),26)

,MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1), Num(Month(FiscalDate)-1 ),26)) as [Fisc_MonthStart]

I include the qvf to show what I have this far. I really appreciate a solution for this.

Thank you.

Re: Fiscal Calendar MonthStart

Lisa_P — Wed, 04 May 2022 05:44:47 GMT

Hi John,

The problem is that when you were doing this calculation and the month was January (1), subtracting 1 gave a 0.

So I added another clause to the if condition like this:

////Issue
,if(month(FiscalDate) > 2,MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))), Num(Month(FiscalDate)-1),26)
, if(Month(FiscalDate)>1, MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1), Num(Month(FiscalDate)-1 ),26)
, MakeDate((Year(YearStart(FiscalDate, 0, $(FirstMonthOfYear)))+1), 1, 26))) as [Fisc_MonthStart]

and it appears to rectify your issue.

Re: Fiscal Calendar MonthStart

johngouws — Wed, 04 May 2022 06:17:30 GMT

Hi @Lisa_P ,

You are sooo clever.. I made one small change and I think it's perfect. It is now:

,if(month(FiscalDate) > 2,MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))), Num(Month(FiscalDate)-1),26)
, if(Month(FiscalDate)>1, MakeDate((Year(YearStart(FiscalDate,0,$(FirstMonthOfYear)))+1), Num(Month(FiscalDate)-1 ),26)
, MakeDate((Year(YearStart(FiscalDate,0, $(FirstMonthOfYear)))), 12, 26))) as [Fisc_MonthStart]

I checked other years and Jan is always correct.

Thank you very much.