8 Replies Latest reply: Jul 25, 2018 6:43 AM by Mohammed Al Radi RSS

    Calendar two date fields inaccurate results

    Mohammed Al Radi

      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!




          "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",




      "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",


              "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,

        "I.D. #",

          'Date Given' as DateType

      Resident [Embroiderers]



        [Date Returned by Embroiderer] as Date,

        "I.D. #",

        'Date Returned' as DateType

      Resident [Embroiderers];



      Load num#(Date,'#') as NumericDate


      RESIDENT DateLink;




      Min(NumericDate) AS MinDate,

      Max(NumericDate) AS MaxDate

      RESIDENT Temp;


      LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

      LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));



      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;