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!
[Embroiderers]:
LOAD
"Embroiderer's Name",
"I.D. #",
"Date of Birth",
"Marital Status",
"Name of Camp",
"Palestinian Town",
"Telephone Number",
"Date First Joined Inaash",
"Speed L/M/H",
"Quality L/M/H",
"Embroidery Material knowhow",
"Preffered Embroidery Items",
"Preffered Embroidery Patterns",
Item,
Quantity,
"Date given to embroiderer" as [Date Given to Embroiderer],
"Date returned by embroiderer" as [Date Returned by Embroiderer],
"Estimated Time Needed",
"Time Unit",
"Fees paid to embroiderer",
"Quality L/M/H2",
Comments,
"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);
DateLink:
LOAD
[Date Given to Embroiderer] as Date,
"I.D. #",
'Date Given' as DateType
Resident [Embroiderers]
;
LOAD
[Date Returned by Embroiderer] as Date,
"I.D. #",
'Date Returned' as DateType
Resident [Embroiderers];
Temp:
Load num#(Date,'#') as NumericDate
RESIDENT DateLink;
MinMax:
LOAD
Min(NumericDate) AS MinDate,
Max(NumericDate) AS MaxDate
RESIDENT Temp;
LET vMinDate = Num(Peek('MinDate',0,'MinMax'));
LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));
MasterCalendar:
LOAD Distinct
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;
