Fiscal Periods / Month Names

Wed, 25 Nov 2020 16:16:04 GMT

Hi There

I have a set of data with the financial year listed as '2015/16', 2016/17 etc, and the organisation I work for uses financial year fiscal periods, so for example fiscal period 01 = July.

I have used the following code to store the FY and min and max fiscal periods into a master calendar:

MasterCalendar:

LOAD

Min(DisFP) as MinFP,

Max(DisFP) as MaxFP,

FY as FinancialYr

Resident TABLE_NAME Group By FY;

LET vMinFP = floor(peek('MinFP'));

LET vMaxFP = floor(peek('MaxFP'));

I am now trying to align the fiscal periods to the Months and use them in a text box to show users the date range the data is applicable for. I don't want the data to change with selection, to only calculate upon the selection of the financial year I want it to remain a static value (almost) as you switch between financial years.

So the formulas I've used in the text box:

=IF(FinancialYr='2016/17','Title used in report' &Month(MinFP) & ' to ' &Month(MaxFP),

IF(FinancialYr='2015/16','Title used in report' &Month(MinFP) & ' to ' &Month(MaxFP)))

Only being fairly new to Qlikview, I've read quite a lot of threads about years etc, but can't seem to get exactly what I am after.

So it is two questions - how do I align my fiscal periods to the months I want (1 = July, 2 = August), or do I need to go back to the script.

Thanks for your help, I can't load up any examples due to organisation policy so hopefully I've provided enough information.

Just wanted to update this that the above master calendar table worked, once I dropped renaming the FY it seemed to link together the way I wanted. Thanks for everyone's responses.

Re: Fiscal Periods / Month Names

adamdavi3s — Thu, 09 Mar 2017 09:15:31 GMT

Is DisFP a date field?

What isn't quite working for you as your formula look OK

Re: Fiscal Periods / Month Names

rahulpawarb — Thu, 09 Mar 2017 10:11:43 GMT

Hello Bronwyn,

You can make use of below field to generate FiscalMonth (Considering Month - 1 = Jan, 2 = Feb... 12 = Dec).

If(Month>=7, Month-6, Month+6) AS FiscalMonth

Hope this will be helpful.

Regards!

Rahul

topic Re: Fiscal Periods / Month Names in QlikView

Fiscal Periods / Month Names

Re: Fiscal Periods / Month Names

Re: Fiscal Periods / Month Names