Table calc filter messing with running_sum calc
Andy Holt Mar 2, 2017 12:36 PMOK, so first-up I'll preface this question by saying that I don't *think* it is possible to achieve what I need, but I am seeking a second opinion and would love to be proved wrong!
I am thinking that the only way to achieve what I need is by having my DB developer build me a custom view of the data in the form that I need, rather than trying to allow Tableau to do the heavy lifting.
But as I say, please tell me I'm wrong
The problem overview is that I am attempting to create a line chart of a running_sum of a series. The running_sum needs to start at the beginning of the calendar year, but the chart only wants to show a specified month.
I'll talk you through my attached workbook where I've attempted to demonstrate my work-flow so far.
[data exploration]
This just shows the data complete with 'holes' and a simple Running Total Quick Table Calculation. By click 'Showing Missing Values' on the Transaction Date field it has padded my data field.
[zeroes for nulls]
By utilising a Lookup table calc I am able to fill the null values in the series with zeroes, and calculate a running sum across that new series.
[chart version]
I wanted to check that this would still work for a line chart, and by changing the Transaction Date to a continuous dimension, this works fine. Zeroes for nulls in the top chart, and all dates shown in the cumulative chart at the bottom.
[Note, the final value of 1,485)
[w/ table calc filter]
Now I want to only show the chart for February. Obviously I cannot use a simple filter on the Transaction Date field because then it will mess up my running_sum calculation, so I need to think about the Tableau order of operations and use a Table Calc Filter so that it filters *after* doing the calculations.
Note the final value of 1,485 is still there, which is good. However something has clearly gone awry.
[Chart comparison]
The problem is more evident here. You can see that where we had the zeroes in the series allowing 'flat bits' in the cumulative chart (on the left) the use of the table calc filter has messed this up and now only dates with transactions are populated (on the right).
All dates are shown (due to using the continuous Transaction Date) but not all dates have values.
So there you have it. Have I missed a trick somewhere or am I correct in thinking that this problem is not solvable given my current data structure?
Thanks a lot
Andy
(Tableau v10.1.3 btw)