Count items based on two dates

nlagas — Sun, 06 Aug 2023 07:46:30 GMT

Hello,

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

Thank you

topic Count items based on two dates in App Development

Count items based on two dates