Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all. Hope someone can point me in the right direction with this one. I am fairly familiar with Power BI but I am still coming to grips with Qlik Sense and I'm not quite sure how to get the right outcome here.
Basically I have a system of tickets, which each move through four stages before completion. When a ticket moves to a new stage, a timestamp is created. The data that I am importing is a chronology of timestamps, e.g.
Time | Ticket | Stage |
06:45:22 | 1 | 1 |
06:46:12 | 2 | 1 |
06:52:33 | 1 | 2 |
06:52:33 | 3 | 1 |
07:04:24 | 2 | 2 |
07:06:14 | 1 | 3 |
07:06:14 | 3 | 2 |
07:12:12 | 2 | 3 |
... | ... | ... |
What I would like to do is to bucket these tickets so that I can create a visualisation that shows how many unique tickets are in each stage at any point in time.
In Power BI/DAX I would create four measures (one for each stage). Each measure would roughly be comprised of the following calculation steps:
I just can't figure out the syntax in Qlik to do it! I am getting very mixed up with AGGR() and ABOVE() functions and none of the results are coming out how I expect. Any help would be much appreciated.
If you would accomplish the same goal in a completely different way (maybe via load transformation), please feel free to suggest a total change of approach - I am tearing my hair out a little bit here and willing to consider anything. 🙂
Apologies for not loading an example file, the data is commercially sensitive.
It seems that you don't need above() nor aggr() here. Set analysis is the way to go with. And if you are new and not much familiar with set analysis, it would be bit difficult. The expressions would look something like:
1. Count( Distinct {<Time={"<=$(=Max(Time))"}>} Ticket)
2. Count( Distinct {<Time={"<=$(=Max(Time))"}, Stage={'$(=Max(Stage)+1)'}>}Ticket)
There would be additional components to be considered in the set analysis based on your data model, like disregarding other time stamp fields, formats of the time fields, ...
It seems that you don't need above() nor aggr() here. Set analysis is the way to go with. And if you are new and not much familiar with set analysis, it would be bit difficult. The expressions would look something like:
1. Count( Distinct {<Time={"<=$(=Max(Time))"}>} Ticket)
2. Count( Distinct {<Time={"<=$(=Max(Time))"}, Stage={'$(=Max(Stage)+1)'}>}Ticket)
There would be additional components to be considered in the set analysis based on your data model, like disregarding other time stamp fields, formats of the time fields, ...
Man, I could kiss you - that's brilliant and elegant and I'm not surprised I was completely headed down the wrong track.
As you say there's a couple of other components I need to sort out, but you've given me the core logic I was struggling with.
Thanks for such a quick solve.