I have a table listing all tickets with an open date and a closed date and a location (I have more fields but not relevant here). I'd like to have one line chart, one line showing how many tickets have been opened based on YearMonth dimension, a second line showing how many tickets have been closed, finally a third showing an accumulation of the difference. Since this is one visualization out of many on one page, I have to keep the filters working on this visualization as well.
[Data]:
ticket, location, dateopen, dateclosed
1, EU, 1-aug-2022, 5-aug-2022
2, EU, 2-aug-2022, 6-sep-2022
3, EU, 3-aug-2022, 7-oct-2022
4, EU, 4-oct-2022, 8-oct-2022
4, US, 5-aug-2022, 9-sep-2022
5, US, 6-sep-2022, 10-sep-2022
6, US, 7-sep-2022, 11-oct-2022
7, US, 8-oct-2022, null
Desired results (as a graph, displayed as a table here for simplicity) - no country filter set
MonthYear, #opened, # closed, accumulated
aug-2022, 4, 1, 3
sep-2022, 2, 3, 2
oct-2022, 2, 3, 1
If i set country filter to EU:
MonthYear, #opened, # closed, accumulated
aug-2022, 3, 1, 2
sep-2022, 0, 1, 1
oct-2022, 1, 2, 0
I found a way to do it but is extremely inefficient and the Qlik takes a very long time to load. I'm sure there is a better way. Shall I do this from load editor directly? Open to any suggestions
