Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Ascalon
Contributor II
Contributor II

Creating a Measure to convert Timestamps to Point-In-Time Buckets

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.

TimeTicketStage
06:45:2211
06:46:1221
06:52:3312
06:52:3331
07:04:2422
07:06:1413
07:06:1432
07:12:1223
.........

 

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:

  1. Dynamically filter the full dataset down to rows appearing on or before a given time context (driven by a time selection from my master time table) and stage I am interested in (e.g. Stage 2), then perform a distinct count of ticket numbers (to give all tickets that had moved into the bucket before that point in time)
  2. Dynamically filter the full dataset down to the same timestamp context and the next stage (in this example, Stage 3), and perform a distinct count of ticket numbers (to give all tickets that had moved out of the bucket before that point in time)
  3. Deduct (2) from (1) to give the number of tickets in the Stage 2 bucket at that point in time

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.

Labels (4)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

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, ...

View solution in original post

2 Replies
tresesco
MVP
MVP

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, ...

Ascalon
Contributor II
Contributor II
Author

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.