0 Replies Latest reply: Jun 16, 2016 2:56 AM by basil filatov RSS

    Smart data aggregation

    basil filatov

      Dear Qlikviewers,

       

      I wish I could get any help there. I have source data looking like:

          

      Ticket_NumberChange_DateTimeChanged_FieldNew_Value
      2400262015-08-31 09:48:55ResponsibleContact Center
      2400262015-08-31 09:48:55State01.Registered
      2400262015-09-02 17:47:35Due Date9/10/2015 10:48:55 AM
      2400262015-09-02 17:47:35ResponsibleGadyrshin, Albert PH/RU
      2400262015-09-02 17:47:35State08.Work In Progress
      2400262015-09-02 17:47:36Priority04 Low
      2400262016-03-04 10:48:56ResponsibleToropchin, Maksim PH/RU
      2400262016-03-14 14:27:41ResponsibleRIM-T1-Service desk
      2400262016-03-15 14:31:16ResponsibleGerbeleva, Yuliya PH/RU/EXT
      2400262016-03-15 14:31:16State14.Done, confirming closure.
      2400262016-03-18 15:41:08Due Date9/15/2015 11:58:47 AM
      2400262016-03-18 15:41:08State15.Closed

       

      The actual table have about 200k strings, limited during load.

       

      What I need is to have the final pivot table looks like:

       

      GroupDate01/0602/0603/0604/0605/0606/06
      RIM-T1-Service deskOpened30151822196
      Closed201732369522
      Work in progress5832829
      Work in progress more 5 days123152118

       

      The problem is that I know the date ticket came to "08.Work in Progress" state and the date when it changed it's state to "14. Done, confirming closure". How to show this ticket as "work in progress" in all the dates it was actually work in progress, and if it was more then 5 days in this state count it in the corresponding field? Current project is attached.