Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Return first month of the Fiscal Year regardless of month chosen

Hello everyone,

Quick q, I am trying to create a code so that regardless of the month you select within your fiscal year, it will always return the first month of that fiscal year (April). So if I selected October 2018, it will return the data for April 2018. Does anyone know how to write the code for this?

Currently my master calendar looks like this:

MasterCalendar: 

Load Distinct

               TempDate as Date,

               Year(TempDate) as Year, 

               Month(TempDate) as Month,

               If(Month(TempDate)=$(vFM),1,0) as AprilFlag,           

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

               Year(TempDate) + If(Month(TempDate)>=$(vFM),1,0) as [Fiscal Year],

               Date(monthstart(TempDate), 'YYYYMM') as YearMonth,

              Year(TempDate) + If(Month(TempDate)=$(vFM),1,0) &

               (Month(TempDate)= 'Apr') as YearAprilFlag,

               Year(TempDate) + If(Month(TempDate)>=$(vFM),1,0) &

               if(Month(TempDate)= 'Apr',01, 

                  if(Month(TempDate)= 'May',02,

                     if(Month(TempDate)= 'Jun',03,

                        if(Month(TempDate)= 'Jul',04,

                           if(Month(TempDate)= 'Aug',05,

                              if(Month(TempDate)= 'Sep',06,

                                 if(Month(TempDate)= 'Oct',07,

                                    if(Month(TempDate)= 'Nov',08,

                                       if(Month(TempDate)= 'Dec',09,

                                          if(Month(TempDate)= 'Jan',10,

                                             if(Month(TempDate)= 'Feb',11,

                                                if(Month(TempDate)= 'Mar',12,

                                                   0)

                                                )

                                             )

                                          )

                                       )

                                    )

                                 )

                              )

                           )

                        )

                     )

                  ) as [Fiscal YearMonth]

Thanks a lot in advance

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Contributor II
Contributor II

Re: Return first month of the Fiscal Year regardless of month chosen

For anyone who is interested, after going through it a few times I found the solution

I created two variables:

vMaxFiscalYear: Max([Fiscal Year])

vSelectApril: Min({<Date=,Month=,Year=,YearMonth=,[Fiscal YearMonth]=,[Fiscal Year]={"=$(=vMaxFiscalYear)"}>}[YearMonth])

View solution in original post

3 Replies

Re: Return first month of the Fiscal Year regardless of month chosen

May be this

MonthName(YearStart(Max(DateField), 0, 4))

Highlighted
Contributor II
Contributor II

Re: Return first month of the Fiscal Year regardless of month chosen

Thank you very much for your reply Sunny.

Unfortunately this wasn't holding April fixed when I selected different months in the Fiscal Year. However, after going over it a few times I found the solution!

Highlighted
Contributor II
Contributor II

Re: Return first month of the Fiscal Year regardless of month chosen

For anyone who is interested, after going through it a few times I found the solution

I created two variables:

vMaxFiscalYear: Max([Fiscal Year])

vSelectApril: Min({<Date=,Month=,Year=,YearMonth=,[Fiscal YearMonth]=,[Fiscal Year]={"=$(=vMaxFiscalYear)"}>}[YearMonth])

View solution in original post