9 November 2018
I don't use the modulo function (which is just % in Tableau) very often, but every so often, I find a really handy solution to something which uses it. In this case, it was a neat way to only show full quarters of historical data.Here's a view of all sales by segment per quarter in the Superstore data:
Nice and simple, no filters or restrictions - these are the sales by segment per quarter across the whole data set.But something I often do for clients is create a date parameter so that they can use a dashboard to generate historical reports, or show what the business looked like at a particular point in time. So, here's a month selection parameter, which allows you to select any particular month in the data:
...and that can be combined with a Last Twelve Months filter, which will filter the data to the twelve months up to and including the month selected in the parameter. So, if you select November 2018, it'll show you all the data between 1st December 2017 and 30th November 2018.
So, let's have a look at what happens when we apply our Last Twelve Months filter to our sales by segment per quarter view:
Hold up a second, that doesn't look right... let's compare it with the unfiltered data:What's happened is that the Last Twelve Months filter has worked exactly as it's supposed to; it's filtered the data from 1st December 2017 to 30th November 2018. But the problem with that is that 2017 Q4 only has one month of data in it, and unless you stop to think about it, you could be misled into thinking that there really were more sales in 2018 Q1 than 2017 Q4.Instead of filtering to exactly the last twelve months, we could filter to however many months it takes to show the current quarter and the previous three full quarters. So, if that's November 2018, that would be October and November 2018 in the current quarter, and the three full quarters before that, from January 2018 to September 2018. If you select October 2018, it'd just be October 2018 in the current quarter, and then also from January 2018 to September 2018 for the three quarters before that.This is where the modulo function comes in. You can create an if statement to change the number of months to filter to depending on how far through the quarter the selected month is. The modulo function returns the remainder left over when you divide by a particular number, so for our purpose, we can use the modulo function with the month number to work out how far through a quarter that month is. There are three months in a quarter, so we can use the month number % 3 to work out what the remainder is when we divide the month number by three. This will return the same number for each month at the same stage of a quarter.For example, the months at the end of a quarter are March (3), June (6), September (9), and December (12). These all divide neatly by three, which means that Month Number % 3 is always going to be zero for the month sat the end of a quarter. The months in the middle of a quarter - February (2), May (5), August (8), and November (11) - don't divide neatly by three, but they do all return a remainder of two when you calculate Month Number % 3. This calculation is a neat way of isolating how far through a quarter a month is, and you can see how it works in this table:
We can use these remainders - 1, 2, and 0 for the first, second, and third months of a quarter - to create a filter with an if statement:
That might be a little small to read properly, so here it is in text for you to copy/paste:IFDATEPART('month', [Month]) % 3 = 0 THENDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) >= 0 ANDDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) < 12ELSEIFDATEPART('month', [Month]) % 3 = 2 THENDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) >= 0 ANDDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) < 11ELSEDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) >= 0 ANDDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) < 10ENDPut that filter on all relevant worksheets and set it to TRUE, and sure enough, we get the current incomplete quarter (or the current complete quarter if you select March, June, September, or December), and the previous three full quarters:
This means you can see full quarters within a custom time range without seeing misleading quarterly data, and without using table calculations or LODs.
Nice and simple, no filters or restrictions - these are the sales by segment per quarter across the whole data set.But something I often do for clients is create a date parameter so that they can use a dashboard to generate historical reports, or show what the business looked like at a particular point in time. So, here's a month selection parameter, which allows you to select any particular month in the data:...and that can be combined with a Last Twelve Months filter, which will filter the data to the twelve months up to and including the month selected in the parameter. So, if you select November 2018, it'll show you all the data between 1st December 2017 and 30th November 2018.So, let's have a look at what happens when we apply our Last Twelve Months filter to our sales by segment per quarter view:Hold up a second, that doesn't look right... let's compare it with the unfiltered data:What's happened is that the Last Twelve Months filter has worked exactly as it's supposed to; it's filtered the data from 1st December 2017 to 30th November 2018. But the problem with that is that 2017 Q4 only has one month of data in it, and unless you stop to think about it, you could be misled into thinking that there really were more sales in 2018 Q1 than 2017 Q4.Instead of filtering to exactly the last twelve months, we could filter to however many months it takes to show the current quarter and the previous three full quarters. So, if that's November 2018, that would be October and November 2018 in the current quarter, and the three full quarters before that, from January 2018 to September 2018. If you select October 2018, it'd just be October 2018 in the current quarter, and then also from January 2018 to September 2018 for the three quarters before that.This is where the modulo function comes in. You can create an if statement to change the number of months to filter to depending on how far through the quarter the selected month is. The modulo function returns the remainder left over when you divide by a particular number, so for our purpose, we can use the modulo function with the month number to work out how far through a quarter that month is. There are three months in a quarter, so we can use the month number % 3 to work out what the remainder is when we divide the month number by three. This will return the same number for each month at the same stage of a quarter.For example, the months at the end of a quarter are March (3), June (6), September (9), and December (12). These all divide neatly by three, which means that Month Number % 3 is always going to be zero for the month sat the end of a quarter. The months in the middle of a quarter - February (2), May (5), August (8), and November (11) - don't divide neatly by three, but they do all return a remainder of two when you calculate Month Number % 3. This calculation is a neat way of isolating how far through a quarter a month is, and you can see how it works in this table:We can use these remainders - 1, 2, and 0 for the first, second, and third months of a quarter - to create a filter with an if statement:That might be a little small to read properly, so here it is in text for you to copy/paste:IFDATEPART('month', [Month]) % 3 = 0 THENDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) >= 0 ANDDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) < 12ELSEIFDATEPART('month', [Month]) % 3 = 2 THENDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) >= 0 ANDDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) < 11ELSEDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) >= 0 ANDDATEDIFF('month', DATETRUNC('month', [Order Date]), [Month]) < 10ENDPut that filter on all relevant worksheets and set it to TRUE, and sure enough, we get the current incomplete quarter (or the current complete quarter if you select March, June, September, or December), and the previous three full quarters:This means you can see full quarters within a custom time range without seeing misleading quarterly data, and without using table calculations or LODs.