Diagonal running sums in a table

Hi there.

In today’s episode of “everything table calc” we will be having a look at how to compute a diagonal running_sum in a table in Tableau.

Though in honesty the use case isn’t really clear to me the fact that someone on the Tableau forums asked precisely this question is an indication that their must be at least one relevant use case.

Also, it is just a nice brain teaser so let’s dive right into it.

What we want to achieve

Though the user called it a running sum you will notice that it is actually not, at least for the inner parts. It is a running sum on row and column = 1 but when on the inner ends, its the product of row value times column value. Still, we could adjust the formulas to reflect whatever we might want to do but for now, let’s stick with the original forums question.

What we would end up with using a “traditional running sum”

If we were to just apply a running sum to our measure like this

//Running sum
running_sum(Sum([Measure]))

We would not be getting very far. Depending on our compute using settings we’d be getting something like this

Or maybe this…

But never would we be able to achieve the desired outcome. So how do we do that?

Let’s set up the data source

You may have noticed that we are using a 4×4 grid in this example. When your datasource has dimension 1 with values A-D and the Measure being all 1 just like this:

You would be getting this for example:

What we need to do here to achieve our grid is to

Create a Cartesian Product / use a cross join

A Cartesian Product or a cross join is a join where we generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join. (Source: sqlshack.com)

In our case, that means we want to do a self-cross-join, i.e. create 16 instead of four rows of values so that each of our dimension members has its counter part.

We do this by connecting to our data source, drag the respective table onto our source pane. Double click the logical layer table to get down to the physical level (i.e. move away from relations / noodles down to where we can use joins); drag the same table onto the canvas again and join the two tables based on a calculated join clause where on both ends we simply insert 1 which then results in 1 = 1 which is always true

Now that our data source is ready to go we need to think about what we want to achieve

1. We want the first column and the first row to be normal running sums
2. From 1 we note that one needs to go table down whilst the other needs to go table across
3. we want our inner part to be a multiplication of the previous row and/or previous column values

Now with this being said the most important part has been described in this blog post https://tableauadventurer.com/2021/09/30/a-quickie-on-nested-table-calculations/

It is important to remember that we can’t do all in one because we need to set multiple different compute directions. So let’s break it down one by one:

First, i need a field that tells me which row I am in and which column I am in. Notice anything? This implies actually two calculated fields so let’s create them.

Create two calculated fields, one being named index h (horizontal) and the other index v (vertical)

//index h
index()

Repeat for index v

The name already gives us the compute direction. Index h will compute table across, index v table down.

With this out of the way we can go on with our next calculation because so far, all we have is the index per row and column.

Now we need to create the calculations that compute our actual values. Again, we need to create two times the same calculation but use different compute directions. So please create

//running_sum h
running_sum(sum([Measure]))

Repeat for running_sum v

So now we have the ability to calculate running_sums down and across.

From here, we can go one step further and create the lookups. As before, we need to fields, one that looks up the value above (vertical) and one to the left (horizontal). Therefore, create to fields like this:

//lookup h
lookup([running_sum h], -1)

As before, recreate the same as lookup v and link to running_sum v.

But this does still not cut it, we need one more field that brings it all together and creates (in this special forums case) the multiplication instead of a simple sum:

//summary
IF[index h] = 1  THEN[running_sum v] 
ELSEIF [index v] = 1 THEN[running_sum h]
ELSE [lookup h] * [lookup v]

END

So here we check if we are either in the first column or the first row and if so, return the running_sum. Note that we are taking the opposite, i.e. for index h we return running_sum v because otherwise, our index check would only be true in the first cell. If we are in any cell where its neither the first column nor the first row then return the multiplication of the lookups.

Now in order to get this all correct, its very important to set the calculation directions. Since we created every step on its own, Tableau enables us to do exactly that. Set the compute using as follows:

index h and running_sum h > table across

index v and running_sum v > table down

lookup h > table down (!)

lookup v > table across (!)

Here is a screen that shows the results step by step. Please pay special attention to how great I am at editing the screens with super nice arrows..

And there you have it, we are done.

I hope you found this useful (and if so, let me know your use case).

Find the workbook here: https://public.tableau.com/app/profile/steffen2460/viz/ForumsDiagonalcalculationsinatable/WITHDETAILS?publish=yes

As always, appreciate your feedback.

Until next time

Steffen