Diagonal running sums in a table – 2nd edition (and why it is actually window sums)

Misunderstandings happen all over the place and Tableau and it’s Forums are no exception to that rule.

Specifically when referring to my last blog entry on the above matter, the provided data was so simplified that my resulting understanding turned out to be wrong.

What the user wanted was not – as I had deduced – a multiplication on the inner parts but he wanted that every cells be the sum of all its adjacent cells (under the condition those cells are more to the left or above the current cell) and itself and those that are those adjacent “parent” cells. What does that mean?

This is what the user actually wanted. Highlighted in yellow is one example. Cell FB in the lower table results from the yellow marked cells in the upper table. The most bottom-right cell in the results table therefore will resemble the total sum of all cells of the base table.

At first my gut feeling said this might get complicated but matter of fact, this one is much easier than the other solution. Still we will be making use of nested table calculations. And as always, remember this is my solution to a problem, it is not THE solution to the problem. There might, and likely will be, others, that also work perfectly fine.

Let’s do this

For the sake of testing I just quickly created a table that looks different to the above example but is good enough for our purposes:

My initial take was to again go for running sums as this feels instantly natural when seeing sums increase based on their predecessors. But at second glance, I decided that actually using window_sums would be the way to go.

Why window_sums and not running_sums?

Let us have a look at the calculations as presented by Tableau: running_sum(expression) does not allow us to define the range within which we want it to work. Contrasting, window_sum(expression, start, end) includes exactly what we want. The ability to clearly define its working range (or: window).

Our window we want to compute is always the quadrant from the upper left corner in the direction of the current cell. Since this window can expand down, right or both, the latter implying a diagonal movement, this tells us we will likely need to nest table calculations.

The notion of nesting table calculations tells us we will have to create our thought out steps / calculations one by one.

This time, we are not going for my loved index but instead use a first() table calculation. Create two of those, name one first across and the other first down. You will find out in a second what we need them for:

//first across
first()

So far, so good. The next step is we know we can move either right or down or both. So that implies we will need to have a nested calculation when moving in the combined direction. Nesting implies we have an inner and an outer calculation. As usual, start with the inner calculation by creating

//window_sum across- inner
window_sum(sum([MEASURE]),[first across],0)

Note this “inner” calculations is also already a nested calculation.

So what does this do? The window_sum “returns the sum of the expression within the window. The window is defined by means of offsets from the current row” ( a direct quote from Tableau help). In our example, this means when we set first across to calculate table across we will get the results for “first()” which look like this:

Now combine this with what we just read on the window_sum and the idea manifests. Assuming we are in Cell AG we would tell Tableau to return to us the window_sum of the sum of our measure (window_sum(sum[MEASURE]… ) starting at an offset of -2 which yields us the first column until end = 0 where 0 always is the cell we are in. So basically, a running_sum if you will.

Next, create the outer window_sum calculation:

//window_sum down - outer
window_sum([window_sum across - inner], [first down], 0)

Notice we are nesting our window_sum across – inner table calculation (which in itself also holds the first across table calculation) as well as the first down table calculation. The idea is exactly as before but we change our measure to be our window_sum across – inner.

Drag the window_sum down – outer onto your table.

The last bit you have to do is make sure all calculations are using the correct computing direction which luckily we have already stated in the names, so click your measure, edit table calculation.

Boom, you are done. Your result should look like this:

And that is it for today.

If you want to follow along find the workbook here: https://public.tableau.com/app/profile/steffen2460/viz/ForumsDiagonalcalculationsinatable-2ndedition/Base

I hope you find that useful (and if so, let me know your use case) . Always appreciate your feedback.

For next times I intend to do a bit more on Workout Wednesday series as I feel I there has been sufficient table calculations postings until this point (though I still got two or three more interesting ones at hand).

Until next time

Steffen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s