Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Smart data aggregation

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.