topic Re: Calendar in QlikView

Calendar

rolandg1881 — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Calendar

miskinmaz — Thu, 03 Oct 2019 11:55:11 GMT

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.

Re: Calendar

rolandg1881 — Thu, 03 Oct 2019 12:04:41 GMT

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

Re: Calendar

miskinmaz — Thu, 03 Oct 2019 12:10:06 GMT

The best way will be :

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

Re: Calendar

Or — Thu, 03 Oct 2019 12:10:25 GMT

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.

Re: Calendar

rolandg1881 — Thu, 03 Oct 2019 12:14:47 GMT

Hi!

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

Greetings

Roland

Re: Calendar

Brett_Bleess — Thu, 10 Oct 2019 13:37:31 GMT

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