This is the third video in the series – Demystifying table calculations in Tableau.
Here, we will look at the second paradoxical problem. If you have not gone through the first paradoxical problem, I recommend you to do it first before proceeding.
Here is the gist of the video:
In the second paradox, we look at another very common business requirement in reporting : looking at figures cumulatively like Year-to-Date (YTD), Month-to-Date (MTD), etc. We have already established that accessing a previous record is difficult for the databases. This paradox also has the same issue, but it adds one more nuance to it.
Here is a text-version of the video for those of you who prefer to read than to watch – please note that this is NOT a transcript of the video.
Let us use the same yearly sales report we have used before. But instead of difference from previous year, let us add a column to display cumulative sales.
Please take a moment to quickly do the calculation. Here is the answer:
Was it hard for you to do it? You would most likely say “It was easy, but not as easy as the first one. Fortunately, the report had rounded numbers which made addition easy, otherwise it could have been a bit cumbersome.”
Now, can you think about why it is hard for the databases? You might be already smiling 🙂 – It is the same reason: We are accessing the previous records. The difference here is that we are not just asking for ONE previous record, but ALL the previous records from the first record in the series.
As we move to each new cell, we are taking on more and more values. For each cell, we are collecting a set of previous values and adding them up. In other words, we are doing a cumulation.
If giving out a single previous record was difficult for the databases, you can imagine the pressure on the databases now :). It is taking the first problem and putting it on steroids.
Let us give this second paradox a name so that we can easily remember its essence: What name would you give? Please take a moment to come up with a good name.
“Cumulation” pops out and it is a great name – we all intuitively understand what cumulation means.
In the tradition of giving ‘tongue-in-cheek’ names, I have come up with the name ‘Connecting the Dots’. This phrase was drilled into my mind by Steve Jobs in his Stanford address. I have a quick detour in my video where I explain the story behind this name. If you are curious, please spend a few minutes and watch the video. If you are pressed for time, please move on and you won’t lose anything.
So, let us summarize the second paradoxical problem:
- Like Peer comparison, looking at figures cumulatively is another instinctive trait in us. We do it all the time.
- Look at the common things a typical business person deals with: YTD Sales, MTD Sales, etc. Even if you work in a business which does not deal with sales, I am sure that you would be looking at YTD or MTD of the most important numbers in your business.
- Please wait till I present you with the grandma script for explaining table calculations and you will see that even grandma uses cumulation routinely in her life.
- Even more complicated things like Moving Average, which is used in the stock markets a lot, is a simple variation of this problem. We are collecting a set of values or a window of values and rather than summing them up, we are doing an average.
Here is a visual summary of the first 2 paradoxes:
You might be already tired of talking about previous values 🙂 – Yes, Me too! For our third problem, let us take our conversation to another Level. See you again one last time in the Paradoxical problem land.