Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
johngouws
Partner
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]

FiscCalendar.JPG

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
Lisa_P
Employee
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.

View solution in original post

2 Replies
Lisa_P
Employee
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.

johngouws
Partner
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. 

FiscCalendar.JPG

Thank you very much.