Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I have a big problem with my calender. For our company i added the fiscal year in my calendar.
When i now choose Fiscal Year 2020 i can only see the revenue for the months July, August and September but there is no June and i dont know why. There is something wrong in my code. Our Fiscal Year starts on 01.06!
When i choose Fiscal Year 2019 i can see the months from June to May and it seems it will work but im not sure.
Can you help me please?
Thank you!
There is the code for my calendar:
TempMinMax:
LOAD
Min(RechBuchDat) as MinDate,
Max(RechBuchDat) as MaxDate
RESIDENT Umsatz;
// Variablen für MinMax Datum
SET vFiscalYearStartMonth = 6; // Number of Month Will be Here for Quarter Start
LET vMinDate = Peek('MinDate', 0, 'TempMinMax');
LET vMaxDate = Peek('MaxDate', 0, 'TempMinMax');
LET vToday = $(vMaxDate);
DROP TABLE TempMinMax;
// Datensätze zwischen min und max generieren
TempCal:
LOAD
date($(vMinDate) + RowNo() -1) AS TempDate
AutoGenerate $(vMaxDate) - (vMinDate) +1;
// Hauptkalender
Hauptkalender:
LOAD
TempDate AS RechBuchDat,
Week(TempDate) AS Woche,
Year(TempDate) AS Jahr,
Dual(Month(TempDate),
Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12)+1) AS FiscalMonth,
YearName(TempDate, 0, $(vFiscalYearStartMonth)) AS FiscalYear,
Year(AddMonths(TempDate,$(vFiscalYearStartMonth))) as [Fiscal Year],
Month(TempDate) AS Monat,
Day(TempDate) AS Tag,
WeekDay(TempDate) AS Wochentag,
'Q' & ceil(month(TempDate) / 3 ) AS Quartal,
InYearToDate(TempDate, $(vToday), 0) * -1 AS JahrAktuell,
InYearToDate(TempDate, $(vToday), -1) * -1 AS JahrVorher
RESIDENT TempCal
ORDER BY TempDate ASC;
DROP TABLE TempCal;
You are creating a calendar by considering the max date in your fact table and this will limit till what day the calendar will be generated.
Check what date Max(RechBuchDat) is returning and alter code likewise to create master calendar as per your requirement.
Hi!
Thank you for the answer! I tried something: Max(RechBuchDat) + 365
But i think it was a bad idea it does not work.
best regards
roland
The best way will be :
Max(addyears(date(RechBuchDat),1) )
It looks like there's a gap in your code between FiscalYear and [Fiscal Year].
FiscalYear looks to be correct.
[Fiscal Year] is not correct. You are adding six months to your date and extracting the year from that. June + 6 months = December, so you're still in the same year as you were before. You would need to add +1 month since you're moving in the opposite direction here.
Hi!
Thank you! In witch row i have to add +1 ?
Greetings
Roland
Roland, the following Design Blog post may be of some help on this one I think:
https://community.qlik.com/t5/Qlik-Design-Blog/Fiscal-Year/ba-p/1472103
Regards,
Brett