Calendar two date fields inaccurate results

[malradi88]

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;

[jonathandienst]

Do the date fields contain a time component? The you will want to floor them before using them in a link

LOAD

    Date(Floor([Date Given to Embroiderer])) as Date,

  "I.D. #",

    'Date Given' as DateType

Resident [Embroiderers];

Also, modify the preload for the date:

Temp:

Load Num(Date) as NumericDate

RESIDENT DateLink;

[malradi88]

Dear Jonathan,

Thank you very much for your reply. I am out of the office but will try your suggestion as soon as I'm back.

There is no time component in the date fields. The format is DD/MM/YYYY

Best,

Mohammed

[malradi88]

Dear Jonathan,

Unfortunately floor did not work

[mdmukramali]

Hi,

Can you attach sample data file.

[malradi88]

Dear Mohammed,

Please find attached the sample. Thank you for your support!

Best,

Mohammed

[mdmukramali]

Hi,

I'm looking at the sample data i found the "I.D. #" is not unique Key in that table .

do you have any other Unique key in that table?

Thanks,

Mohammed Mukram

[mdmukramali]

Hi Al Radi,

I was trying to Use Key field in DateLink Table as Primary Key but Unfortunately in the excel file i didn't found "Embroiderer's Name" . so can you add in the sample file "Embroiderer's Name" .

"I.D. #" & '+' & "Embroiderer's Name" & '+' & Item & '+' & Quantity as Key

Thanks,

Mohammed Mukram

[malradi88]

Dear Mohammed,

Thanks for your reply. Unfortunately I cannot include the embroiderer's name field in the sample file, but the I.D is also an identifier field. Even in the version I am using, I tried using the Key you provided (with embroiderer name) but it is still producing inaccurate results.

Best,

Mohammed