How To: QlikView Running Counts Using FirstSortedValue

I can tell you from first-hand experience, getting running counts or balances to calculate accurately in QlikView is not easy. There are data model concerns and then complicated aggregations to deal with. In this post, I will give you a front-end solution “template” for handling running balances in QlikView discussing the nuances with a few use-cases. I will focus on the use of the FirstSortedValue and Aggr functions.

This will work in both QlikView and Qlik Sense. You can download the application I used along with the spreadsheet source here.

FirstSortedValue-Testing-2.zip

Running Balances: Use Cases Full of Nuance

From a front-end perspective, running counts have not generally been a “one size fits all” solution. For example, if you are reporting a single number: Current Balance, that is easy enough using set analysis. But a different expression would be used for Current Balances by Account. And yet another calculation would be used for Balances by Account Over Time. If you want the user to be able to look at the balances daily rather than monthly, change to select a historical date, or cycle date dimensions (alternative dimensions in Qlik Sense) you are really in trouble.

I have created somewhat of a template expression that can be adapted to most use-cases.

Before We Get to the Fun Stuff: Data Model Concerns

Your data model must first be built to support the questions the user will need to answer. Configuring the data model to support a small question like “How many tickets do I have open today?” is very simple. Building the data model to support a question like “How many tickets were open each day by status in the month of June?” is more complex.

Your data model should house one row for each countable dimension id for each time value that is pertinent for that id.

You should build a row in your data model for each dimension id (ie: Ticket ID, Account Number) for each value of time increment required. For example, if we are reporting on open helpdesk tickets by day, we will need a table that contains a row for every ticket id for every day the ticket was open. If we are only reporting this by month, we will only need a row for each ticket for each month.

The specifics of this consideration will greatly affect the size of our data model. Consider an app with 5 years of history, an average of 500 tickets open at a time with each ticket being in an opened status for 5 days. We will require a table with 4.5 million records (5 years * 365 days * 500 ticket average * 5 days open average).

If the data is not already presented like that, there are techniques to make this happen. We will save them for another post. I will assume for now that you can get your data set up as described above.

FirstSortedValue Solution Template

I think the best approach is to look at the solution template and its components. Then we can apply it to some use cases. This will create accurate running totals in any chart with any combination of dimensions which allows us to run totals for a point in time or over time.

Template:

if(max(Date_Base)=max(total <$(='[‘ & getobjectfield(0) & ‘]’)> Date_Base),
firstsortedvalue(aggr(sum(total < Date_Base,Secondary_Dim> m_ticket), Date_Base, Secondary_Dim), –aggr(max(Date_Base), Secondary_Dim, Date_Base)))

Things you will need to adjust to your needs:

sum(m_ticket)Use your base expression. This would normally be a count or a sum. You will need to insert the total and associated group by field(s)  into your expression.
<$(= [ & getobjectfield(0) & ])>Your decision here is to use as-is or omit. This section is needed when the chart is run over time. This assumes that the date dimension is the primary (first) dimension in the chart.
Date_BaseAnywhere we see Date_Base (6 spots), you will need to substitute your “finest” time dimension from your data model or specifically, the time increment where the expression would evaluate correctly. This has nothing to do with any dimensions you decide to utilize in your chart.
Secondary_Dim[,nth_Dim]Where you see Owner, you will substitute any secondary dimensions from your chart that are not related to the Date dimension separated by commas.

I can attempt to explain how this works.

Without regard to the actual dimensions in the chart, sum up the tickets grouped by the finest date and secondary chart dimensions aggregated by date and secondary dimensions. Then sort this list in descending order by finest date field aggregated by date and secondary dimensions as well. Now consider each chart dimension combination, finding the first value from the sorted list that can be applied to each dimension row in the chart.

Simple, right?

Front-End Scenarios Using FirstSortedValue

So now we can apply this expression to some examples ranging from simple to complex.

Reporting Current Open Tickets with No Dimension

In this first example, we are actually making it more complicated than it has to be simply because we do not have to consider any dimensions. You could use the expression above or a small set to find the maximum date.

Here is the set example of this without using the universal expression.

Here is the example using the universal FirstSortedValue expression:

Note that I omitted the  <$(= [ & getobjectfield(0) & ])> and there are no secondary dimensions in the total fields or aggr lists.

Reporting Current Open Tickets with Dimensions (not time) 

Here is a slightly more complicated example where I have added a dimension (or more than one dimension). But the key here is that this would be the latest count. We are not wanting (yet) to look at the open tickets over time.

The only difference here is that I have added another field to the total and aggr parameters. You will add whatever fields you have used as dimensions in the chart. Here is the chart with the Ticket dimension.

Reporting Running Count of Open Tickets Over Time

Another class of use-cases would be to establish a count of the open tickets over time. It is this kind of scenario that can get challenging without having this template. In the first example, we will look at the count of Tickets by Status over months.

In this case, I went ahead and subbed the actual time dimension field name so you can see how this works. The Month field corresponds to the first dimension of the chart which I assume would be the time dimension. In addition, I have added the secondary dimension of Status since we want to sum up the tickets by month AND by status.

Reporting Running Count of Open Tickets Over Time with Dynamic Date Field

Of course, I couldn’t stop there. Because my users will want to either drill down or cycle their date field so that they can view the chart by Date or by Week or by Month or by Quarter or by Year. This presents a special challenge because we must actually refer to the dimensions used in the chart within the expression.

So I respond with a special function that will retrieve this field name for me as long as the date cycle is the first field in the chart. GetObjectField will retrieve the chart dimension field name. A parameter of 0 will retrieve the first field. 1 will retrieve the second field, etc.

Here is the final expression for the dynamic date interval chart.

And the chart looks like this. Now we have the ability to add a cycle button to handle the date. And most importantly, it will still be accurate as we change the time dimension.

Final Thoughts

This was quite a struggle to get right. And I am hoping I didn’t make a mistake. But I am equally hopeful that if there is a mistake here or the expression can be further streamlined or optimized that the expert LivingQlik readers out there will help me out.

I HAVE A BIG FAVOR TO ASK YOU!

My goal is to be make LivingQlik a super-helpful resource for as many Qlikies as possible. That involves writing the best possible articles and getting as many eyeballs as we possibly can get on to the site. To that end, if you find LivingQlik to be helpful or informative or entertaining or at least mildly interesting, I am asking you to immediately tell some of your friends and colleagues. Just walk down to the next cubicle and tell your fellow developers to check out our humble site. Or share it on the social platform of your choice or send a link to a friend. Please do it now!

Thanks for reading LivingQlik.

Happy Qliking!

Comments on this entry are closed.

  • Steen S Persson April 19, 2017, 6:20 am

    Hi Aaron,

    Nice solution :-).
    It seems like the Excel source file is missing in the zip file, so would it be possible to add it?

    Regards
    Steen

  • Aaron Couron April 19, 2017, 9:52 am

    My mistake. The zip file is fixed. Thanks for letting me know 🙂

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons