Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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
Highlighted
Creator III
Creator III

Re: Calendar

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.

Highlighted
Contributor III
Contributor III

Re: Calendar

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

Highlighted
Creator III
Creator III

Re: Calendar

The best way will be :

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

Highlighted
Specialist III
Specialist III

Re: Calendar

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.

Highlighted
Contributor III
Contributor III

Re: Calendar

Hi!

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

Greetings

Roland

Highlighted
Digital Support
Digital Support

Re: Calendar

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.