Dear Qlik Community I hope this finds you well. I have the follow script, enclosed below, that I have used to distinguish the two date fields from one another (date given to embroiderer & date returned by embroiderer). The 'datetype' field is the flag I am using but unfortunately the script is not working.
The results i have for 'Quantity' (meaning quantity of items produced) and 'fees paid to embroiderer' are both wrong when I filter in the front end (date returned by embroiderer is the main one i am filtering for because they get paid once they produce the item). I have tried different combinations of date functions in the master calendar, re-formating the dates in the actual data source and putting more shared fields between the main 'embroiderer' table and the 'datelink table' but the results are still off by a lot.
In the front end, I have tried normal sum expressions and also sum(aggr(sum but no luck.
The only thing that seems to produce accurate results is if I delete one of the date fields and remove the datelink table so that it is just a normal master calendar (this led me to believe that the problem is with the datelink table...because when I delete it and remove one date field the results seem to be accurate when I filter by year/month year etc...). But it is important that both date fields remain in the app.
Any suggestions or tips on how to keep both date fields, and have a 'datetype' filter that actually works would be most appreciated. I have tried this script with another app and it seemed to work so I am not sure what the problem is.
Thank you for your support!
"Date of Birth",
"Name of Camp",
"Date First Joined Inaash",
"Embroidery Material knowhow",
"Preffered Embroidery Items",
"Preffered Embroidery Patterns",
"Date given to embroiderer" as [Date Given to Embroiderer],
"Date returned by embroiderer" as [Date Returned by Embroiderer],
"Estimated Time Needed",
"Fees paid to embroiderer",
"I.D. #" & '+' & "Embroiderer's Name" & '+' & Item & '+' & Quantity as Key
FROM [lib://xxx (xxx-qlik-01_qliksupport)/xxx-S-Lebanon2.xlsx]
(ooxml, embedded labels, table is Sheet1);
[Date Given to Embroiderer] as Date,
'Date Given' as DateType
[Date Returned by Embroiderer] as Date,
'Date Returned' as DateType
Load num#(Date,'#') as NumericDate
Min(NumericDate) AS MinDate,
Max(NumericDate) AS MaxDate
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
Date ($(vMinDate) + RecNo() - 1) AS Date,
Year(Date($(vMinDate) + RecNo() - 1)) as [Year],
Month(Date($(vMinDate) + RecNo() - 1)) as [Month],
Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MM-YYYY') AS [MonthYear],
'Q'&ceil(month(Date($(vMinDate) + RecNo() - 1))/3)&'-'&Year(Date($(vMinDate) + RecNo() - 1)) As QuarterYear
AUTOGENERATE ($(vMaxDate) - $(vMinDate) +1);
DROP TABLE MinMax;
DROP TABLE Temp;