Logically I understand very well the needed task but problem with the qlik language.
I have to create a dashboard which shows a canonical calendar which show the total quantity of items, which have a begin and end of guarante date respectively, are outside of the selected date-year. ( I created alredy the master calendar and bridge)
Time Lapse is from 2012 - 2028 with a monthly step.
Basically Condition is the following:
Count ( If ( BeginGuarantee < StartMonthYear and EndGuarantee > EndMonthYear)
For example:
| Item |
BeginGuarantee |
EndGuarantee |
| 1 |
01/Jan/2014 |
01/Jan/2016 |
| 2 |
01/April/2014 |
01/April/2016 |
| 3 |
01/July/2014 |
01/July/2016 |
desired Result:
| Month - Year |
Items_in_Guarantee |
| Jan 2014 |
0 |
| Feb 2014 |
1 |
| March 2014 |
1 |
| April 2014 |
1 |
| May 2014 |
2 |
| June 2014 |
2 |
| July 2014 |
2 |
| August 2014 |
3 |
| September 2014 |
3 |
| Oktober 2014 |
3 |
Code is the following:
LoksInfo:
LOAD
"# - FL" as LokID,
"Lok-Nr - FL" as LokNummer,
AutoNumberHash128("# - FL"&'|'&"Lok-Nr - FL") as Key,
"Halter - FL" as Holder,
"Kunde - FL" as Künde,
"Var. - FL" as Variante,
Date("GWL-Beginn - FL", 'MMM/D/YYYY') as GWLBegin,
Date("GWL-Ende - FL", 'MMM/D/YYYY') as GWLEnde
FROM [lib://DataFiles/Datei_Quelle_probe.xlsx]
(ooxml, embedded labels, table is Fertigungsliste);
GWL_Begin:
LOAD
Date("GWL-Beginn - FL", 'MMM/D/YYYY') as GWLBegin,
Year("GWL-Beginn - FL") as GWLBegin_Year,
Month("GWL-Beginn - FL") as GWLBegin_Month,
Day("GWL-Beginn - FL") as GWLBegin_Day,
Date(MonthStart("GWL-Beginn - FL"), 'MMM/D/YYYY') as GWLBegin_StartMonthYear,
Date(MonthEnd("GWL-Beginn - FL"), 'MMM/D/YYYY') as GWLBegin_EndeMonthYear
FROM [lib://DataFiles/Datei_Quelle_probe.xlsx]
(ooxml, embedded labels, table is Fertigungsliste);
GWL_Ende:
LOAD
Date("GWL-Ende - FL", 'MMM/D/YYYY') as GWLEnde,
Year("GWL-Ende - FL") as GWLEnde_Year,
Month("GWL-Ende - FL") as GWLEnde_Month,
Day("GWL-Ende - FL") as GWLEnde_Day,
Date(MonthStart("GWL-Ende - FL"), 'MMM/D/YYYY') as GWLEnde_StartMonthYear,
Date(MonthEnd("GWL-Ende - FL"), 'MMM/D/YYYY') as GWLEnde_EndeMonthYear
FROM [lib://DataFiles/Datei_Quelle_probe.xlsx]
(ooxml, embedded labels, table is Fertigungsliste);
DataBridge:
LOAD
Key as Key,
GWLBegin as CanonicalDate,
'Begin' as FlagDate
Resident LoksInfo;
Load
Key as Key,
GWLEnde as CanonicalDate,
'Ende' as FlagDate
Resident LoksInfo;
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
date(Min(CanonicalDate) , 'MMM/D/YYYY') as minDate,
date(Max(CanonicalDate) , 'MMM/D/YYYY') as maxDate
Resident DataBridge;
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, 'MMM/D/YYYY') as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar_02:
Load
TempDate AS CanonicalDate,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
Date(MonthStart(TempDate), 'MMM/D/YYYY') as MonthYearStart,
Date(MonthEnd(TempDate), 'MMM/D/YYYY') as MonthYearEnd,
Date(TempDate, 'MMM/YYYY') as MonthYear,
Date(MonthStart(TempDate,1), 'MMM/D/YYYY') as NextStart
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
