3 Replies Latest reply on Jun 26, 2015 6:07 AM by Jonathan Drummey

# Is there a way to make a calculation for one month, a different calculation for the three previous months and then take a percentage of each of those month sets to create a final result for the 4 month total period?

Is there a way to make a calculation for one month, a different calculation for the three previous months and then take a percentage of each of those month sets to create a final result for the 4 month total period?

1 month (10*x) = a

3 (different months) (40*y) = b

to get a result of the 4 months (a*30%) + (b*70%) = c

• ###### 1. Re: Is there a way to make a calculation for one month, a different calculation for the three previous months and then take a percentage of each of those month sets to create a final result for the 4 month total period?

I have been able to get answers for a and b.  But I am struggling getting the answer for c.

• ###### 2. Re: Is there a way to make a calculation for one month, a different calculation for the three previous months and then take a percentage of each of those month sets to create a final result for the 4 month total period?

Part C can be done in custom SQL or you could have a DBA create a database view.

• ###### 3. Re: Is there a way to make a calculation for one month, a different calculation for the three previous months and then take a percentage of each of those month sets to create a final result for the 4 month total period?

Tableau's table calculations are designed for this sort of task:

Here's the formula for the 4 month total:

SUM([Sales]) * .3 + ZN(WINDOW_SUM(SUM([Sales]),-3,-1)) * .7

This sums up the sales for each Month and multiplies that by .3. The WINDOW_SUM() is a table calculation that can have offsets from the current address (the month), so it's computing the sum of sales across the prior 3 months and multiplying that total by .7. The outer ZN() returns 0 for the first row (January 2010) because that WINDOW_SUM() will return Null there because there are no prior months. The Compute Using for this table calculation is on the Order Date.

If you want to filter for a specific month, then another table calculation is necessary, I used LOOKUP(MIN([Order Date]), 0). This is because table calculations are computed late in Tableau's pipeline, a regular dimension filter on the Order Date would be applied earlier and filter out the data we need for the table calculation. Since table calculation filters are applied after table calculations are computed, this ends up with an accurate result:

There are at least few other solutions to this: One is the custom query/view that Tableautester proposed, however that has a limitation that filters applied in Tableau won't necessarily affect the results (depending on how the query is built). Another option is to use a parameter for choosing the anchor month and then a calculated field to generate the 4 month total, however parameters aren't dynamic. And there's hack-y solution using a self-data blend to create something like a dynamic parameter.

I've attached a v8.2/3 workbook with the two worksheets. If you have any questions, let me know!

Jonathan