How to determine fiscal month using today's date?

[posywang]

Hi, I am new to Qlikview and just went through developer training last week, so I am still practicing what I learnt from class. Our company has a 4-4-5 fiscal calendar as shown below. I was trying to determine fiscal month using today's date but not being successful. Can anyone please help me out on this one? For example, today is 4/14/2016, I'd like the application to return 'April' or '4'. Thank you in advance!

Month	From	To
January	12/14/2015	1/17/2016
February	1/18/2016	2/14/2016
March	2/15/2016	3/13/2016
April	3/14/2016	4/17/2016
May	4/18/2016	5/15/2016
June	5/16/2016	6/12/2016
July	6/13/2016	7/17/2016
August	7/18/2016	8/14/2016
September	8/15/2016	9/11/2016
October	9/12/2016	10/16/2016
November	10/17/2016	11/13/2016
December	11/14/2016	12/11/2016

[sunny_talwar]

For getting April You can try this:

Month(Today())

and for 4, you can try this:

Num(Month(Today()))

[posywang]

Hmmm, but for 4/18/2016, I'd like to get May or 5 according to attached fiscal month table...

[johnw]

Well, below is an answer. But I would think that what you really need is a formula to return the 4-4-5 month given a date, rather than using a table like this. I don't know what the formula would be, so I'd just be searching the web, and I'm not sure I'd be answering your actual question. And if you are pulling it from a calendar rather than a formula, you wouldn't want it in your script like this, you'd want to load it from Excel or a database or however that calendar is being stored. The inline script is just to make a simple example using your actual data.

LOAD Month as [Current Fiscal Month]
INLINE [
Month From To
January 12/14/2015 1/17/2016
February 1/18/2016 2/14/2016
March 2/15/2016 3/13/2016
April 3/14/2016 4/17/2016
May 4/18/2016 5/15/2016
June 5/16/2016 6/12/2016
July 6/13/2016 7/17/2016
August 7/18/2016 8/14/2016
September 8/15/2016 9/11/2016
October 9/12/2016 10/16/2016
November 10/17/2016 11/13/2016
December 11/14/2016 12/11/2016
] (delimiter is ' ')
WHERE today() >= From
AND today() <= To
;

[clondono]

Yo can use this expression to get the current fiscal month based on your calendar:

if(Date(Today()) >= Date(From) and Date(Today()) <= Date(To), Month,'')

See attached

[clondono · Accepted solution]

You can test that it works by adding a variable for the date instead of today:

if(Date(vSelectDate) >= Date(From) and Date(vSelectDate) <= Date(To), Month,'')

[Peter_Cammaert]

"Quick'n'Dirty" Load script for a 5-4-4 Calendar. Challenge: add some flexibility by calculating the vCycles value from the number of periods between vFirstDate and Today().

SET LongMonthNames = 'January;February;March;April;May;June;July;August;September;October;November;December';

LET vFirstDate = '12/14/2015';

LET vCycles = 12;

BaseFiscalPeriodTable: // 5-4-4 Calendar without 53-week years

LOAD SubField('$(LongMonthNames)', ';', RowNo()) AS Month,

     date(IF (RowNo() = 1, '$(vFirstDate)', peek('To') + 1)) AS From,

     date(IF (RowNo() = 1, '$(vFirstDate)', peek('To') + 1)+IF (mod(RowNo(), 3)=1, 34, 27)) AS To

AUTOGENERATE $(vCycles);

Best,

Peter

[chinnuchinni]

Hi, 

How can I find the current and next month sales based on this calender .

can you please help me on this .

Regards

PC

[chinnuchinni]

Hi,

Can you please help me on this.

How to calculate the current and next month sales based on this calender.

Regards

PC