13 Replies Latest reply: Jul 3, 2017 3:15 PM by Stefan Wühl RSS

    Help regarding carryon count of tickets

    qvhelp please

      Hi All,

       

      in the attached sheet there are fields ticket number, created date and resolved date and ticket status

      my requirement is

       

      lets says total tickets raised in a month is 100 and 12 of them is resolved by last day of that month the carry forward should be 88 that has to be added to the tickets on the month start of next month and it has to go on

       

      can we achieve something like that ,

       

      Thanks in Advance

      stalwar1kush141087gwassenaarswuehlmaxgrofranky_h79

        • Re: Help regarding carryon count of tickets
          Stefan Wühl

          You could look into creating reference dates for each date in the interval between creation and closure of a ticket, like shown in

          Creating Reference Dates for Intervals

           

          The reference date table should show the granularity that is required for your reports, dates when you need a count of open tickets per date or maybe months if you only need the count per month.

            • Re: Help regarding carryon count of tickets
              qvhelp please

              Hi swuehl ,

               

              I am not able figure out how can do this ,i fyou have free time can you please make try with the sample data i attached .

               

              Thanks Again .

                • Re: Help regarding carryon count of tickets
                  Stefan Wühl

                  It should be something like

                   

                   

                   

                  Tickets:

                  LOAD ticket_id,

                       CreatedDateText,

                       Ticket_ResolveDate,

                       ticket_status,

                       Alt(Ticket_ResolveDate,Today()) as EndDate

                  FROM

                  [commhelp.xls]

                  (biff, embedded labels, table is Sheet1$);

                   

                   

                  ReferenceDates:

                  LOAD ticket_id,

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

                  RESIDENT Tickets

                  WHILE CreatedDateText+iterno()-1 <= EndDate;

                   

                  Then use ReferenceDate as dimenion e.g. in a bar chart and

                  =Count(DISTINCT ticket_id)

                   

                  as expression to count the open tickets.

                    • Re: Help regarding carryon count of tickets
                      qvhelp please

                      Hi

                       

                      i fear that i didnt explained correctly what my requirement is

                       

                      i would like to explain again COmmpic.PNG

                      in the above pic total tickets raised on that particular month dec are 84 and total of tickets that got resolved are 84 but this does not mean that all the tickets that are raised in dec are resolved the tickets that have been raised before December might get resolved in dec

                       

                      so my requirement is lets say in the month DEC 100 tickets are been raised and 85 of them are resolved by the monthend of DEC on ,and 25 tickets are raised in JAN and ten of them got resolved by the month end of jan


                      i want to plot a graph in Bar chat  like this ,Hope this explains my requirement


                      Carry ticketspng.png

                        • Re: Help regarding carryon count of tickets
                          Stefan Wühl

                          For example, you can create flags in the table with reference dates to indicate creation and closing dates.

                           

                          Then you can easily count creation or closing of tickets next to your open count.

                           

                          Intervalmatch Count Open Items

                            • Re: Help regarding carryon count of tickets
                              qvhelp please

                              i read the the whole article  , but i am unable to apply it here ,

                               

                              Comminflow3.PNG

                               

                              my total ticets on every months has to be same as Total With Remaining (total tickets raised on that month +carryfarward tickets from lastmonth) ,can you try to attach an app if possible ,sorry to ask in weekends

                                • Re: Help regarding carryon count of tickets
                                  Stefan Wühl

                                  Using the script from the other thread and some UI chart objects:

                                   

                                  2016-06-27 01_06_58-QlikView x64 - [C__Users_Stefan_Downloads_comm222092.qvw].png

                                    • Re: Help regarding carryon count of tickets
                                      qvhelp please

                                      we are almost there but when i check the total tickets that are open it is showing me as 35 where as the logic we implemented is giving us 31 , so we are missing four tickets here

                                        • Re: Help regarding carryon count of tickets
                                          Stefan Wühl

                                          We are not missing any tickets.

                                           

                                          The 35 tickets are giving the number of tickets that were open any time in the selected time frame, in your example, 35 are the total number of distinct ticket_id.

                                           

                                          The 31 tickets are the giving the  number of tickets at the end of your selected time frame (end of June 2016).

                                           

                                          There is a difference of four tickets, because 4 tickets have been closed in Apr/May/June 2016.

                                            • Re: Help regarding carryon count of tickets
                                              qvhelp please

                                              i have to appreciate your patience with my silly questions still i have a doubt that 35 tickets i am showing there active tickets and if i take set analysis only for the month of june it is showing me 33 . usually the active tickets has to be same .i our scenario it should be 35 i believe

                                                • Re: Help regarding carryon count of tickets
                                                  Stefan Wühl

                                                  I think the difference is cause by the fact that some tickets are in status active, but show a resolved date before today (even before the month of today, June):

                                                   

                                                  ticket_id ticket_status createddate resolveddate Ticket_ResolveDate
                                                  193928Active08.04.201614.04.201614.04.2016
                                                  345724Active16.02.201619.05.201619.05.2016
                                                  206709Active13.06.201615.06.201615.06.2016
                                                  204790Active02.06.201628.06.2016 
                                                  205323Active06.06.201628.06.2016 
                                                  205547Active07.06.201628.06.2016 
                                                  206308Active10.06.201628.06.2016 
                                                  207162Active15.06.201628.06.2016 
                                                  207373Active15.06.201628.06.2016 
                                                  343563Active04.02.201628.06.2016 
                                                  343890Active07.02.201628.06.2016 
                                                  349779Active04.03.201628.06.2016 
                                                  357198Active06.04.201628.06.2016 
                                                  365952Active04.05.201628.06.2016 
                                                  374811Active10.06.201628.06.2016 
                                                  375956Active15.06.201628.06.2016 
                                                  375968Active15.06.201628.06.2016 
                                                  512279Active29.04.201628.06.2016 
                                                  513139Active02.05.201628.06.2016 
                                                  526337Active29.05.201628.06.2016 
                                                  526873Active31.05.201628.06.2016 
                                                  527498Active01.06.201628.06.2016 
                                                  528859Active05.06.201628.06.2016 
                                                  529251Active06.06.201628.06.2016 
                                                  533326Active14.06.201628.06.2016

                                                   

                                                  The first two tickets should explain the difference of two.

                                                    • Re: Help regarding carryon count of tickets
                                                      Robert Lakin

                                                      Thanks for this, really helped!

                                                       

                                                      How would I leave weekends out of the reference date caculations?

                                                       

                                                      LinkTable:

                                                      LOAD *,

                                                      MonthName(ReferenceDate) as ReferenceMonthName,

                                                      WeekName(ReferenceDate) as ReferenceWeekName,

                                                      WeekDay(ReferenceDate) as ReferenceWeekDAYName

                                                      WHERE ReferenceDate <= Today();

                                                      LOAD

                                                        [Case Number],

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

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

                                                          If(Date(createddate+iterno()-1) = resolveddate, 1,0) as Flag_Resolved,

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

                                                      Resident Cases

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