17 Replies Latest reply: Jun 1, 2016 11:34 AM by Joey Lutes RSS

    Intervalmatch Count Open Items

    Joey Lutes

      I've been messing with this for 7+ days and I think I'm close, but could use a boost over the fence please:

       

      Here are the details:

      1 table:  id, created_when, validated_when + various other fields.

      master calendar/island.

       

      OBJECTIVE:

      Combo chart with 3 elements:

      Date as dimension

      Line 1:  Items created that day

      Line 2:  Items closed that day

      ...so far so good.  I created some flags, works fine.

      Bars across the bottom, I'd like to show total OPEN(outstanding) items on that day.

      Suffice to say, if an item is created on 2/1, closed on 2/10, then from 2/1-2/09 it would be among the counted 'open'.

       

      I *believe* IntervalMatch is the fix for this - but can't seem to make it work.  Again, I have 40+ hours of iterations with this, so I've read a lot and tried even more.
      Here is my code.  I originally loaded these tables to create my flags, but was hoping they'd double for intervalmatch.  maybe not?  The code produces no synthetic keys and does seem to link properly, and maybe I just don't know the fields to call in the chart measure?
      Help??

      OpenValidatedDate:

      Load

      id,

      id as OVID,

      created_when as CreatedDate,

      created_when as Date,

      1 as Flag_Created,

      Resident rfi;

       

       

      Concatenate (OpenValidatedDate)

      Load

      id,

      id as OVID,

      validated_when as ValidatedDate,

      validated_when as Date,

      1 as Flag_ValidatedDate

      Resident rfi

      where exists (validated_when);

       

       

       

       

       

      IntervalMatch:

      IntervalMatch (Date)

      Left join

      Load distinct CreatedDate, ValidatedDate

      resident OpenValidatedDate;

        • Re: Intervalmatch Count Open Items
          Stefan Wühl

          Have a look at this blog post:

          Creating Reference Dates for Intervals

           

          use the reference date as dimension in your charts, then

          =Count(DISTINCT id)

            • Re: Intervalmatch Count Open Items
              Joey Lutes

              Thanks for the quick response, but I don't think I must have explained myself well.  I tried that and it's not quite what I'm looking for.

              I'll try again:

               

              ID     created     validated

              1          2/1               2/5

              2          2/3               2/7

              3          2/3                 -

              4          2/5               2/6

              5          2/6                -

               

              I want a chart that shows all dates as the dimension (Master Calendar?) and a count of open items as such:

               

              Date     Open items

              2/1               1

              2/2               1

              2/3               3

              2/4               3

              2/5               3  (id 4 added, id 1 validated - ids 2, 3, 4 open)

              2/6               3  (id 5 added, id 4 validated - ids 2, 3, 5 open)

               

              Basically, a running net total of open items per day of the year.

              Hope that makes sense?

               

              Thanks so much for the quick reply.

                • Re: Intervalmatch Count Open Items
                  Stefan Wühl

                  Right, I believe that's exactely what's demonstrated in HICs blog post.

                   

                  If you apply the recipe with the WHILE loop, your table (you would need to fill in an end date for validated without value, like today())

                   

                  ID     created     validated

                  1          2/1               2/5

                  2          2/3               2/7

                  3          2/3                 -

                  4          2/5               2/6

                  5          2/6                -

                   

                  will transform to an additional table with fields

                   

                  ID, Date

                   

                  with one record per ID and date within date range between created and validated.

                   

                  Then you create a master calendar and link to Date field.

                   

                  Done.

                   

                  Use Date as dimension and

                  =Count(DISTINCT ID)

                   

                  as count for the open IDs per Date.

                   

                  You can use calendar fields to drill up to week, month, year as needed.

                   

                  BTW, an Intervalmatch LOAD prefix matching your master calendar to the date ranges (again, with end dates added for open intervals) will basically create the same, but I think the solution with the WHILE loop is much more simple.

                   

                  Best

                  Stefan

                    • Re: Intervalmatch Count Open Items
                      Stefan Wühl

                      Basically, it looks like

                       

                       

                       

                      DateRange:

                      LOAD ID, created, If(Len(Trim(validated)),validated,Today()) as validated;

                      LOAD ID,

                        SetDateYear(Date#(created,'M/D'),2016) as created,

                        SetDateYear(Date#(validated,'M/D'),2016) as validated

                      INLINE [

                      ID,     created,     validated

                      1,          2/1,               2/5

                      2,          2/3,               2/7

                      3,          2/3,                

                      4,          2/5,               2/6

                      5,          2/6,               

                      ];

                       

                       

                      LinkTable:

                      LOAD ID, Date(created+iterno()-1) as Date

                      Resident DateRange

                      WHILE created+iterno()-1 <= validated;

                        • Re: Intervalmatch Count Open Items
                          Joey Lutes

                          Thanks so much Stefan,

                           

                          I think we're close - I appreciate the help more than I can express

                          Few questions I've come up with (as always!):

                           

                          Can you tell me what the purpose of the SetDateYear statements are for?

                          I wonder why the 2 Load statements?  I notice if I remove the 2nd 'Load', it fails, so I left it in.

                          id is used in a variety of places, would this need to be a separate id (id as otherid) as it's a new table?

                           

                          Here's what I have with actual fieldnames.  Unfortunately, I'm getting an error that's stumping me.  error is in the bold area and reads:

                          Field not found - <created_when>: LOAD id, SetDateYear(Date#(created_when,'M/D'),2016) as created, SetDateYear(Date#(validated_when,'M/D'),2016) as validated resident rfi

                           

                          created_when is the accurate fieldname and I seem to just be missing the problem.

                          rfi is the original datasource so I assumed the actual load would be a resident load of that?

                           

                          DateRange:

                          LOAD

                               id,

                              created_when,

                              If(Len(Trim(validated_when)), validated_when, Today()) as validated;

                          LOAD

                              id,

                             SetDateYear(Date#(created_when,'M/D'),2016) as created,

                             SetDateYear(Date#(validated_when,'M/D'),2016) as validated

                          resident rfi;

                           

                          LinkTable:

                          LOAD id, Date(created_when+iterno()-1) as Date

                          Resident DateRange

                          WHILE created_when+iterno()-1 <= validated;

                           

                          thoughts?

                           

                           

                          ....EDIT....

                           

                          I made a few changes which resulted in a synthetic key, then more which relieved it. 

                          The first thing I did was to comment out the 2nd load statement.  That ALMOST got us there.  The data was VERY close to being accurate, though there were areas where it wasn't as I tested it against the data source.

                          I know we're close . . .    Here are my iterations:

                           

                          CURRENT::

                           

                          DateRange:

                          LOAD

                            id as DateRangeID,

                              created_when,

                            If(Len(Trim(validated_when)), validated_when, Today()) as validated

                          // LOAD

                          // id,

                          //   SetDateYear(Date#(created_when,'M/D'),2016) as created,

                          //   SetDateYear(Date#(validated_when,'M/D'),2016) as validated

                          resident rfi;

                           

                          LinkTable:

                          LOAD

                              DateRangeID,

                              DateRangeID as LinkID,

                            Date(created_when+iterno()-1) as LinkCreatedDate

                          Resident DateRange

                          WHILE created_when+iterno()-1 <= validated;

                          ------------------------------------------------------------------------------------------------------------

                          PREVIOUS:

                           

                          DateRange:

                          LOAD

                               id,

                              created_when,

                            If(Len(Trim(validated_when)), validated_when, Today()) as validated

                          // LOAD

                          // id,

                          //   SetDateYear(Date#(created_when,'M/D'),2016) as created,

                          //   SetDateYear(Date#(validated_when,'M/D'),2016) as validated

                          resident rfi;

                           

                          LinkTable:

                          LOAD

                              id,

                            Date(created_when+iterno()-1) as Date

                          Resident DateRange

                          WHILE created_when+iterno()-1 <= validated;

                           

                          I can see t his will get me where I'm going, just almost there.

                          Thanks again!

                            • Re: Intervalmatch Count Open Items
                              Stefan Wühl

                              Your sample data misses year information in the date fields, so I think if you just interpret month and date, Qlik will assume 1900 as year for your sample data.

                              But to generate the accurate date range (especially when we replace missing end dates with today()!), we should explicitely state the year of your records.

                               

                              SetDateYear() is just a function that changes / setting the year for a date value. Have a look at the Date & Time functions in Qlik, there are a lot of them and there are different approaches to interpret the correct date from your text values.

                               

                              You need to take care to adapt the Date#() interpretation function to your input field format (or if they come in as numbers, just format them as date).

                               

                              See also

                              Why don’t my dates work?

                              Get the Dates Right

                               

                              You can do the transformation also in 1 LOAD step (without the preceding LOAD), which should perform better, but I tend to write it using the preceding LOAD for clarity.

                               

                              If the field created_when is not found, have you maybe renamed the field or qualified the table in the LOAD that created rfi table? Have a look at the table viewer to see how the complete field name looks like.

                               

                              I would also suggest that you do the date transformation in the original rfi table LOAD (or don't forget to DROP rfi table to avoid having a mostly duplicate table in your data model).

                               

                              If you are running into synthetic keys, then probably because other tables show same field names, assumingly Date, that link to both new rfi table and DateLink table?

                               

                              To help you more, you would need to post at least a screen shot of your data model.

                                • Re: Intervalmatch Count Open Items
                                  Joey Lutes

                                  Ok, thanks a lot.

                                  Here's where we are today:

                                   

                                  DateRange:

                                  LOAD

                                      id,

                                      created_when as rangecreated,

                                    If(Len(Trim(validated_when)), validated_when, Today()) as rangevalidated

                                  resident rfi;

                                   

                                  LinkTable:

                                  LOAD

                                    id,

                                    Date(rangecreated+iterno()-1) as ReferenceDate

                                  Resident DateRange

                                  WHILE rangecreated+iterno()-1 <= rangevalidated;

                                   

                                  My dates do have year - just didn't post that, so I removed that part.

                                  As you see, I changed the fieldnames to make them unique, which has eliminated all synthetic errors.

                                  No code issues currently, script loads fine, and when I count(distinct id), it ALMOST works!

                                  The other weird issue is, when I use Date as my dimension (which is originally from the MasterCalendar, it only populates dates where events occur, not all of them now.  interesting behavior.

                                   

                                  The problem I'm seeing is that in comparing the results to the actual data, it's just off in some places by a small amount.

                                   

                                  I've attached a screenshot of my data model for you, also a spreadsheet showing what Qlik Sense is displaying (#N/A means no data displayed), and what the actual values should be.

                                   

                                  Again, SO close and thank you!

                                   

                                  datamodelss.JPG

                                   

                                  ss.JPG

                                    • Re: Intervalmatch Count Open Items
                                      Stefan Wühl

                                      You need to use the ReferenceDate as the dimension when you want to count the open IDs.

                                       

                                      You may want to create a master calendar also for the ReferenceDate, if you want to drill up to lower granular calendar fields.

                                       

                                      See also

                                      Tutorial - Using Common Date Dimensions and Shared Calendars

                                       

                                      If this does not fix your issue, then could you maybe post a small sample QVD?

                                        • Re: Intervalmatch Count Open Items
                                          Joey Lutes

                                          I read the tutorial and chased the rabbit hole into canonical dates, multiple master calendars - just not sure which direction is correct.  Changing to using the ReferenceDate as the dimension made things worse.

                                           

                                          I'm attaching my qvf so you can peek at what I have.  The actual graph is on the RFI Regional Summary tab, the sandbox I'm using is Sandbox2

                                          Thank you

                                            • Re: Intervalmatch Count Open Items
                                              Stefan Wühl

                                              Maybe I have looked at the wrong charts, but I can't see that you are using ReferenceDate as dimension in you combo chart. The solution I suggested will not create the correct open count if you are using a different date field that is only linked by ID to the DateLink table.

                                               

                                              I see that you also want to show the sum of created and validated IDs in your chart. To be able to do this easily, create flags for this in the DateLink table:

                                               

                                              DateRange:

                                              LOAD ID, created, If(Len(Trim(validated)),validated,Today()) as validated;

                                              LOAD ID,

                                                SetDateYear(Date#(created,'M/D'),2016) as created,

                                                SetDateYear(Date#(validated,'M/D'),2016) as validated

                                              INLINE [

                                              ID,     created,     validated

                                              1,          2/1,               2/5

                                              2,          2/3,               2/7

                                              3,          2/3,               

                                              4,          2/5,               2/6

                                              5,          2/6,              

                                              ];

                                               

                                               

                                              LinkTable:

                                              LOAD ID, Date(created+iterno()-1) as ReferenceDate,

                                              If(iterno()=1, 1,0) as CreatedFlag,

                                                If(Date(created+iterno()-1) = validated, 1,0) as ValidatedFlag

                                              Resident DateRange

                                              WHILE created+iterno()-1 <= validated;


                                               

                                              Then link your master calendar to the ReferenceDate, use ReferenceDate as dimension and as expressions

                                              =Count(DISTINCT ID)

                                              =Sum(CreatedFlag)

                                              =Sum(ValidatedFlag)


                                                • Re: Intervalmatch Count Open Items
                                                  Joey Lutes

                                                  Hi Stefan,

                                                   

                                                  Ok, so I took your suggestions.  Please see updated qvf.

                                                  If you'll look at the Sandbox2 tab, it will show you that it's close, but

                                                  not quite accurate.  I advise you to choose 'Port in SmartEdge & N&SIS

                                                  Check' as a 'Rule Name' as it narrows the data substantially.

                                                  If you'll noticed, there are no validated dates until 2/19, so the count

                                                  should be incrementing for each created item on dates prior, and it's not

                                                  consistently.  I'm not sure what it's doing.

                                                   

                                                  I did make a change to the master calendar as referenced in 'Main'

                                                  (TempDate as ReferenceDate,)

                                                  Thoughts?

                                                   

                                                  Thanks again.

                                                    • Re: Intervalmatch Count Open Items
                                                      Stefan Wühl

                                                      I currently can't see an anomaly. I count 9 created until 2/19 and the open id count shows 9 on that date.

                                                      Also the dates before seem ok to me. Could you point me specific to a date or ID that is not correct?

                                                       

                                                      edit: note that our current script code creates a ReferenceDate for all dates between created and validated, including these dates.

                                                      If you don't want this, we can change it.

                                • Re: Intervalmatch Count Open Items
                                  Joey Lutes

                                  I'm not sure what I was seeing - you are correct.  So if you select the Rule name 'Description SOEM vs Smartedge' it lays it out pretty well.  40 (all total) created on 2/10, then slowly starts validating them.

                                  What I do notice is that it shows the validation happens the day after - ie, if it validates on 2/14, it will show the reduction on 2/15.  This is an acceptable issue but if you know a quick way to solve that, theoretically the validation should reduce the total on the same day it's validated.

                                   

                                  As far as the dates go, I think we'll need the ability to control the dates a bit more - would we need a separate master calendar to link to all possible dates or?  I notice that my ranges are limited in some instances (due to no created/validated dates before/after certain days), and the user will prefer to limit their date dimension view themselves.

                                   

                                  I can't thank you enough for all your help.  This has been a godsend.

                                    • Re: Intervalmatch Count Open Items
                                      Stefan Wühl

                                      Ok, that's good that my eyes still working

                                       

                                      For your open count to exclude the validation date, maybe create an explicite open Flag:

                                       

                                      LOAD ID, Date(created+iterno()-1) as ReferenceDate,

                                      If(iterno()=1, 1,0) as CreatedFlag,

                                        If(Date(created+iterno()-1) = validated, 1,0) as ValidatedFlag,

                                        If(created+iterno()-1 < validated,1,0) as OpenFlag

                                      Resident DateRange

                                      WHILE created+iterno()-1 <= validated;

                                       

                                      Then Sum(OpenFlag) instead doing a Count(DISTINCT ID)

                                       

                                      And yes, I think you should create a master calendar for ReferenceDate, I thought you've already done this, how have you created it?

                                       

                                      The Master Calendar

                                        • Re: Intervalmatch Count Open Items
                                          Joey Lutes

                                          Hi Stefan,

                                           

                                          Jury's still out on the other calendar.  I still don't quite understand how to integrate them or if I even need another one.  Another concept therein is the canonical date which I've played with, but is currently commented out.  Again, struggling with concept a bit there.

                                           

                                          Based on your last post, the graph is working nearly perfectly, thank you!  For some reason, 'today', Cleared(validated) and Open swap for some reason.  Strange behavior, and only on the current day (there is no actual data past 5/11 in this dataset).  Would you know why?

                                           

                                          DateRange:

                                          LOAD

                                              id,

                                              created_when as createddate,

                                            If(Len(Trim(validated_when)), validated_when, Today()) as validateddate

                                          resident rfi;

                                           

                                          LinkTable:

                                          LOAD

                                            id,

                                            Date(createddate+iterno()-1) as ReferenceDate,

                                              If(iterno()=1, 1,0) as Flag_Created,

                                              If(Date(createddate+iterno()-1) = validateddate, 1,0) as Flag_Validated,

                                              If(createddate+iterno()-1 < validateddate,1,0) as Flag_Open

                                          Resident DateRange

                                          WHILE createddate+iterno()-1 <= validateddate;

                                           

                                          Capture2.JPG

                                            • Re: Intervalmatch Count Open Items
                                              Stefan Wühl

                                              Yes, that's due to the way that we create Today() as validateddate for every ID without a validateddate in the source data, than later on, we create the Flag_Validated for that date (though it should be still open logic wise).

                                               

                                              Maybe we can fix this by setting the validateddate for these ID to 1 day in the future, then filter the reference dates up to today:

                                               

                                              DateRange:

                                              LOAD

                                                  id,

                                                  created_when as createddate,

                                                If(Len(Trim(validated_when)), validated_when, Today()+1) as validateddate

                                              resident rfi;

                                               

                                              LinkTable:

                                              LOAD *

                                              WHERE ReferenceDate <= Today();

                                              LOAD

                                                id,

                                                Date(createddate+iterno()-1) as ReferenceDate,

                                                  If(iterno()=1, 1,0) as Flag_Created,

                                                  If(Date(createddate+iterno()-1) = validateddate, 1,0) as Flag_Validated,

                                                  If(createddate+iterno()-1 < validateddate,1,0) as Flag_Open

                                              Resident DateRange

                                              WHILE createddate+iterno()-1 <= validateddate;

                                                • Re: Intervalmatch Count Open Items
                                                  Joey Lutes

                                                  Looks like that did the trick.  The Today()+1 makes sense, though I wouldn't have figured out the Load * where ReferenceDate <= Today().  Basically it's telling it to load all (only) data from yesterday and before, correct?  I would have put that statement after we set the ReferenceDate name.  I still have a lot to learn.

                                                   

                                                  Thanks again for all your amazing help.  I think we're good

                                                   

                                                  Cheers,

                                                   

                                                  Joey