7 Replies Latest reply: Jan 12, 2018 1:57 PM by Marco Wedel RSS

    One table, Two Date Fields and one Backlog

    Gianluca Ambrogi

      Hi all,
      I am new in QLIK community and I am quite new in qlik experience. I have read the post "One table, Two Date Fields"  ( https://community.qlik.com/thread/136729?_ga=2.147659761.733376340.1515509018-2108658744.1514547763 ) about the solution to show 2 date fields in the same Chart.


      I have a similar problem, I have to show in a chart a incident log table with 3 columns (two date fields that can contain different dates):
      - incident number
      - incident open date
      - incident close date

       

      I need to create a chart in which, for each calendar day, I show the total count of opened and closed incident.

      And this is simple, implementing the same solution described in the post "One table, Two Date Fields" (see Marco Wedelsolution).


      But I need a new more element to show in the chart: the "incident backlog", I mean the historical number of incident "still open" for each day.

       

      The formula is:  "backlog day1" = "backlog day0" + "total count opened incident day 1" - "total count closed incident day 1"


      I can't find out how to calculate this backlog.

       

      Someone can help me?

        • Re: One table, Two Date Fields and one Backlog
          Sunny Talwar

          Would you be able to provide some data with the expected output to help you better?

            • Re: One table, Two Date Fields and one Backlog
              Gianluca Ambrogi

              Hi!

              First of all thanks for the support.

               

              Here you can find some data and the script.

               

               

              Incident NumberIncident Open DateIncident Close Date
              IN0517212602-12-201609-12-2016
              IN0517212802-12-201612-12-2016
              IN0517264702-12-201609-12-2016
              IN0517389609-12-201612-12-2016
              IN0517431509-12-201609-12-2016
              IN0517495809-12-2016
              IN0517503509-12-201609-12-2016
              IN0517503609-12-201609-12-2016
              IN0517503609-12-201609-12-2016
              IN0517628009-12-2016
              IN0517652209-12-201616-12-2016
              IN0517657809-12-201618-01-2017
              IN0517676309-12-2016
              IN0517694709-12-201608-03-2017
              IN0517725209-12-201611-01-2017
              IN0517776009-12-201620-12-2016
              IN0517783309-12-201625-01-2017
              IN0519161812-12-201612-12-2016
              IN0519163912-12-201612-12-2016
              IN0519185712-12-201612-12-2016
              IN0519267712-12-2016
              IN0519396012-12-201627-12-2016

               

              //////////////////////////////////////////////////////////////////////////////////////////////////////

              tabIncident:

              LOAD

                  [Incident Number],

                  [Incident Open Date] as Opened,

                  [Incident Close Date] as Closed

              FROM

              $(XLS_FILE)

              (ooxml, embedded labels, table is Incident);

               

              tabLinkIncident:

              CrossTable (IncidentTrend, Date)

              LOAD

                  [Incident Reference Number],

                  Opened,

                  Closed

              Resident tabIncident;

               

              tabCalIncident:

              LOAD *,

                  Day(Date) as Day,

                  WeekDay(Date) as WeekDay,

                  Week(Date) as Week,

                  WeekName(Date) as WeekName,

                  Month(Date) as Month,

                  MonthName(Date) as MonthName,

                  Ceil(Month(Date)/3) as Quarter,

                  QuarterName(Date) as QuarterName,

                  Year(Date) as Year,

                  WeekYear(Date) as WeekYear;

              LOAD Date(MinDate+IterNo()-1) as Date

              While MinDate+IterNo()-1<=Today();

              LOAD RangeMin(Min(Opened),Min(Closed)) as MinDate

              Resident tabIncident;

              //////////////////////////////////////////////////////////////////////////////////////////////////////

               

              Here the Chart I did.

              Incident Trend.jpg

               

              Here the Chart with the BackLog (grey line) I would like to obtain.

              Incident Trend with Backlog.jpg

            • Re: One table, Two Date Fields and one Backlog
              Kamal Naithani

              Hi Gianluca,

               

              I think what you can do :

              You can take second expression in the same chart as per the formula you shared above.

               

              Then show your output by selecting Style tab in chart option with Stacked.

               

              Hope this help you

               

              Regards

              Kamal