# App Development

Announcements
cancel
Showing results for
Did you mean:
Partner

## Fiscal Calendar MonthStart

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 26th February to the following year 25th 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.

Labels (1)

1 Solution

Accepted Solutions
Employee

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.

2 Replies
Employee

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.

Partner
Author

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.