- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Running Total with all periods
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- 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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Vitali,
As to your message, heres a new app.
Johan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.