Hi altogether,
I am using Qlik Sense Desktop 1.1.0 and have the following problem.
I want to show some data about orders. All data from a certain year is shown in a separate line chart. Important is, that also for the current year all twelve months should be shown, even if there is no order yet, i.e. June till December 2015 should be shown as zero values.
I created a master calendar and connected it to the order table by the same field name ‘OrderDate’.
When I start the app the first time everything looks fine (see Screen 1). In the first line Chart for the current year, the months June to Dec are shown with 0 values. But as soon as I click on the filter panel and select a product number, only the months are shown where the product was sold, but no longer the months June to Dec. (see screen 2).
You can also see in the table on the right side that shows all OrderDates, that without filtering there are no gaps in the dates. As soon the article is filtered there are gaps, i.e. some days are missing where the article was not sold.
Anybody has an idea what is going wrong?
Thanks
-------------------
The data for the line chart for the current year is :
Dimension Field:
MonthYear
Measure Field:
Sum({$<Year={2015}>} nNumber)
Script of order table:
LOAD kBestellung,
tRechnung_kRechnung,
tBenutzer_kBenutzer,
tAdresse_kAdresse,
tText_kText,
tKunde_kKunde,
cBestellNr,
cType,
date(floor(dErstellt)) as OrderDate, //cut off time and convert to date format again
…
MMYY,
YYYY,
MM;
SQL SELECT kBestellung,
tRechnung_kRechnung,
tBenutzer_kBenutzer,
tAdresse_kAdresse,
tText_kText,
tKunde_kKunde,
cBestellNr,
cType,
dErstellt,
…
Right('00'+Convert(varchar, DATEPART(month, [dErstellt])), 2)+'/'+cast(DATEPART(yy, [dErstellt]) as varchar(4)) as MMYY,
Year(dErstellt) as YYYY,
Right('00'+Convert(varchar, DATEPART(month, [dErstellt])), 2) as MM
FROM torder;
Master calendar script:
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
LET varMinDate = Floor(MakeDate(year(AddYears(Today(),-2)), 1 ,1)); //Heute vor 2 Jahren 1.1.13 /floor statt num
LET varMaxDate = Floor(MakeDate(year(Today()),12,31)); //Heute bis Ende dieses Jahres
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS OrderDate,
WeekYear(TempDate) &'/ S'& week(TempDate) AS id_Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) &'/'& Year(TempDate) AS MonthYear,
Month(TempDate) As Month,
Day(TempDate) As Day,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
