Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying to add Financial Years into my existing Master Calendar and having done some browsing and reading it appears there are a few methods but I could not see one with the right answer
I want to maintain the calendar below but also want to add in FY as an extra option is this a possibility?
Toby
/////////////////////////////
// QLIKVIEW MASTER CALENDAR//
/////////////////////////////
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min(CMDBDate) as minDate, // <<<<< Change Name of Date Field Here
max(CMDBDate) as maxDate // <<<<< Change Name of Date Field Here
Resident ReportView;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS CMDBDate, // <<<<< Change Name of Date Field Here
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
WeekEnd(TempDate) as WeekEndDate,
WeekStart(TempDate) as WeekStartDate,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Thank you very very much!
Sorry Sasidhar that did not work and is bringing back some weird combinations lol
hi please can you share a sample or what combinations is it bringing?
how are you selecting your dates?
Sasi
Hi Sasidhar,
It was bringing back combinations such as 2013/2013 and 2016/2016 not just the standard FY setup
Toby
Hi
Please, could you give me some example dates where your are getting 2013/2013 or 2016/2016 combination?
thx
Sasi
Hi
I think I know the problem.. you need a if condition here
please try
=if(Year(addmonths(addyears(TempDate,1),-3)) =Year(TempDate),year(addyears(TempDate,1)) &'/'& year(TempDate),Year(addmonths(addyears(TempDate,1),-3)) &'/' & Year(TempDate))
hth
Sasi
Apologies for the late response.
Where would I put this statement within the Master Calendar?
replace
Year(addmonths(addyears(TempDate,1),-3)) &'/' & Year(TempDate) as FiscalYearDisplay
with
if(Year(addmonths(addyears(TempDate,1),-3)) =Year(TempDate),year(addyears(TempDate,1)) &'/'& year(TempDate),Year(addmonths(addyears(TempDate,1),-3)) &'/' & Year(TempDate)) as FiscalYearDisplay
hth
Sasi
Thank you again Sasidhar!!!
How can I show it the other way round? as it shows 2015/2016 as 2016/2015?
Toby
=if(Year(addmonths(addyears(TempDate,1),-3)) =Year(TempDate),year(TempDate) &'/'& year(addyears(TempDate,1)), Year(TempDate)&'/' & Year(addmonths(addyears(TempDate,1),-3)))