Running Total with all periods

[Vitali]

I hope you can help me,

I've been going through the forums a lot but I come running in circles.

As a sample dataset, let's say I have the following structure:

Column1: Item id

Column2: Date In

Column3: Date Out

Column4: Item Category

I need to have the finest level of granularity (daily) of current balance of items. I understand that I need to use a calendar table to 'fill in' the missing dates. I manage to pull running totals but my major problem is that when I filter to a specific period, I lose the amount, accumulated beforehand. I guess I need to 'pre-calculate' the running total and filter onto it but I really cannot find the solution or approach on the forums. The closest I've seen is a post for Qlik View with Full Accumulation by @MarcoWedel  and I've tried to recreate this in Qlik Sense but the problem still remains.

Below are three tables I built in Excel which represent as follows (file enclosed as well).

 Initial state, Current State (equivalent of what I have in Qlik), and desired Output.

I really need some advice.

Thank you very much in advance

Vitali

[johanlindell · Accepted solution]

Hi  Vitali,

Here's an app with two solutions, one with an aggregated calendar and one where "empty" transactions are added for days with no transaction and a running total is calculated for each item / project.

I hope one of these solutions fit you. They will have different performance characteristics depending of the data you have.

If you use the date-aggr you can combine that with the date-set-expression if you need to integrate it in an app with a date selection:

Sum ({<[Date aggr] = P(Date), $(=Concat ({1<$Table = {'Master calendar'},>} '[' & $Field & '] =', ', '))>} Quantity)

I hope you will get it to work. :).

Have a great Easter

Johan

[Vitali]

Thank you very much! I think I'll be using the Date-aggr approach as Solution 1 generates a row for each Project-Date-Item combination and in the production dataset we're talking about a rather large time period (several years) with dozens of projects.
The Date-Aggr works with single date-column and this is something I already have in the server, I think it will be easier to implement.
Sincere thanks, @Johan Lindell!

[Vitali]

If anyone is using the solution, here are a couple of things to keep in mind:
- the formula had an extra coma, removed below:
Sum ({<[Date aggr] = P(Date), $(=Concat ({1<$Table = {'Master calendar'}>} '[' & $Field & '] =', ', '))>} Quantity)
- your calendar may have a different name - make sure to use the proper one when reusing the formula;
- Leave $Field as it is;
- Date Aggr is the date field in the Master calendar;

[johanlindell]

Hi Vitali,

As to your message, heres a new app.

Johan

[Vitali]

Thank you very much, I believe this is it. Looking at the syntax, it doesn't seem complicated and yet, it wouldn't come to my mind to write it... What would be the best book you'd recommend reading - there's no QS for dummies 🙂

For the community: in a PM I sent to Johan, I provided a Power BI sample of what I was aiming to build in Qlik Sense. The report can be found here, it uses the same data as the beginning of the post.

Power BI report with Sample Data