Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rolandg1881
Creator
Creator

Calendar

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;

6 Replies
miskinmaz
Creator III
Creator III

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.

rolandg1881
Creator
Creator
Author

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

miskinmaz
Creator III
Creator III

The best way will be :

Max(addyears(date(RechBuchDat),1) )

Or
MVP
MVP

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.

rolandg1881
Creator
Creator
Author

Hi!

Thank you! In witch row i have to add +1 ?

Greetings

Roland

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.