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!

       

      [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;