Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Is DisFP a date field?
What isn't quite working for you as your formula look OK
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