Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Oguzhan
Contributor III
Contributor III

Running Balance based on Date Fields

Hello,

i have following Situation. I have a table withe TicketID field and these Tickets can have a Status from 10 to 60 and each of the statuses have a completion date. For the statuses itself its not necessary that the statuses gets upgraded in chronological order, that means for example if a Ticket ist rejected it can go from status 10 directly to status 40 without hitting other statuses.

The problem i have is to visualize the running balances using a  line chart. I wann to see how many Tickets were in each status in each  month without acumulating the values. With the the given table for example in January I have  8 Tickets wich were created in Jan.20 altough some ticketsStatus changed by now. For Feb.20 I need the value of 5 Tickets because back in Feb.20 these Tickets were in Status20. An so on for my whole dateset.

I have really now Idea how to beginn with. Thank you all in advance for any Help

Labels (5)
2 Replies
Dalton_Ruer
Support
Support

I would flatten your data out in the load script something like this:

Tickets:
LOAD
TicketID,
TicketStatus,
Status10,
Status20,
Status25,
Status30,
Status40,
Status50,
Status55,
Status60
FROM [lib://AttachedFiles/Test_Running_Balance.xlsx]
(ooxml, embedded labels, table is Tabelle1);

Flattened:
Load
TicketID,
'Status10' as Status,
Status10 as Month
Resident Tickets
Where Status10 <> '-';

// Concatenate
Load
TicketID,
'Status20' as Status,
Status20 as Month
Resident Tickets
Where Status20 <> '-';

That would yield a table that allowed you to use the MONTH as the dimension and the status values as a dimension as well. 

Dalton_Ruer_1-1620827013287.png

 

 

Oguzhan
Contributor III
Contributor III
Author

Thank you for your repsonse, the provided solution works well but I guess my requierement was a little bit missleading. I always want  to count the max status per month and ID.

Also if the status of one ticketID hasn't changed in one of the following months the status should still be counted.

My requirement is to see the number of tickets per status in each month.

E.g. Jan.20->Status10: 6 (1,2,3,6,7,10,12,13 -> 8 status10 - (1,2) -> 2 Status > 10) ;

Feb.20->Status10: 5 (4,5,8,9 ->  4 Status10 + 6 from Jan.20 = 10 -  (4,5,6,7,9) -> 5 Status >10) ... so on for the whole data set.

Thank you in advance