A running total in a dynamically filtered table

[Stouenborg]

Hi Everyone

I hope there is some help in this forum for an issue I have struggled with for a while. I have searched wide and near for answers without luck.

I have a table with time registrations from a group of employees. Each row contains the Employee (ref), Project (ref), WorkType (ref), and Duration (calculated by [EndTime]-[StartTime]).
Have a Dashboard view with the table of all the registrations, and reference views that allow me to select a project, work type, and/or employee. The registration table filters nicely depending on my selection in the three reference views. All Good.

Here is the problem: I need to calculate the Duration for the rows displayed in the Registration table and update it with the selected filtering.

How can I make this happen?

I have tried various solutions, but it always calculates the Duration for all rows, even the ones hidden by the filter.

I really hope you can help.

[WillowMobileSys]

---

@Stouenborg wrote:

Here is the problem: I need to calculate the Duration for the rows displayed in the Registration table and update it with the selected filtering.

How can I make this happen?

---

I assume you are using the Interactive Dashboard for the filtering? 

Unfortunately, this feature doesn't provide the ability to "see" which rows are selected in the other views.  Your calculation would need to know this so the same filtering can be applied in the expression.

You might be able to use a Grouped Table View.  Maybe you can apply the Grouping such that it encompases all of the filtered rows.  If so then you can choose to show an aggregate function with the Grouping header that is a Sum of the Durations from each row in the view.  See images below.

If this doesn't meet your needs then you may need to build your owning filtering mechanism using a Filter Selection panel that is then applied to a Slice that dynamically chooses the rows used in your Data view.  Then you will have the filtered Slice rows you can apply your calculation to.

Interactive Dashboard showing all rows

Interactive Dashboard showing filtered rows

[Stouenborg]

You are right that it is currently an interactive dashboard.
I'll have a look at Grouped Table View and Filter Selection panel - see if they can solve this. Thank you for the suggestions.

[1minManager]

So the virtual column you've got to add up the duration takes no account of the in-app filtering.

They way I do this is to create a new table with from and to dates.  In that new table create a virtual column to pull in records from the registration table referencing these two date columns.

Simon, 1minManager.com

[Stouenborg]

Hi Simon
There is currently no need for filtering based on dates.
There is, however, a need to filter on (up to) three different settings (Project, Employee, and WorkType). I'm not sure how to solve this using your idea. Can you elaborate?
The "Duration" is not a virtual column. It is a calculated value stored in a column. It is the total of the durations I need, but only for the filtered rows.

[WillowMobileSys · Accepted solution]

---

@Stouenborg wrote:

There is, however, a need to filter on (up to) three different settings (Project, Employee, and WorkType). I'm not sure how to solve this using your idea. Can you elaborate?
The "Duration" is not a virtual column. It is a calculated value stored in a column.

---

Since you are filtering by 3 different categories - Project, Employee and WorkType - you wouldn't be able to obtain a single clear Total Dashboard amount from just a single Grouped Table View. 

And there are several different permutations of filter selections.  In this case, if it is critical to obtain a clear Total Duration value no matter how the data is filtered, then I would have to recommend the usage of a custom filtering panel with a Slice.

It is not too terribly hard to implement just extra steps.   The idea is to create a Filter table with the 3 categories to filter by.  Then a Filter Form is presented in the Dashboard.  When the user saves the filter row, a Slice uses the row to filter the data.  You are then free to include whatever visual components you wish to provide the resulting filtered data and totals.

[Stouenborg]

Building that filter expression was interesting - but it was a success. And (naturally) when I showed the progress, then the request came for adding dates as filters 🙂
Thank you all for your help.

[WillowMobileSys]

---

@Stouenborg wrote:

And (naturally) when I showed the progress, then the request came for adding dates as filters

---

On the plus side, once you have the main filtering structure in place, it is easy to scale.  Just add a new filter column to the Filter table and adjust the filter expression in the Slice.