4 Replies Latest reply on Oct 4, 2018 1:30 AM by Arnau Forne

Moving Annual Totals

Hi,

I need to create Moving Annual totals for each month in order to create a chart like the one shown below.

The MAT periods show become Dimensions as later on I need to add them in a Parameter along with Months, YTD, etc

In the database I only have months and sales and what I'm missing is to be able to create the MATs (green bit)

I attached the excel file.

Arnau

• 1. Re: Moving Annual Totals

Hi

is this what you wanted

use the formula shown and set to Null if not enough values

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• 2. Re: Moving Annual Totals

Hi Jim,

It works, thanks! Veru useful!

The next step, if possible, would be to transform this Measure into a Dimension. This way I would be able to use it as a Date dimension and add it in a Parameter along with Year, YTD, Month.

I guess here the question is whether we can also create a Rolling 12 month as a Date dimension. Make sense?

Many thanks!

Arnau

• 3. Re: Moving Annual Totals

Hi

Running totals are table calculations that are at the bottom of the order of operation - converting to a dimension is not going to work

so what can you do - Parameters in tableau are static values -  they don't do anything in themselves - they must be included in a filter or a calculation -

Not certain what the real goal here -   what is it you want to show in the viz - an example would be helpful

Jim

1 of 1 people found this helpful
• 4. Re: Moving Annual Totals

Hi Jim,

I found a solution for that.

Step 1: Create a Calculation for Latest Date

{FIXED: MAX([PERIODS])}

Step 2: Calculation for MAT

IF [MAT TEST]='MAT' THEN

IF YEAR([PERIODS])=YEAR([Latest Date TEST]) THEN

'MAT-'+STR(YEAR([PERIODS]))

ELSEIF [PERIODS] <= DATEADD('year',-1,[Latest Date TEST])

AND [PERIODS] >= DATETRUNC('year',DATEADD('year',-1,[Latest Date TEST]))

THEN 'MAT-'+STR(YEAR([PERIODS]))

ELSEIF [PERIODS] <= DATEADD('year',-2,[Latest Date TEST])

AND [PERIODS] >= DATETRUNC('year',DATEADD('year',-2,[Latest Date TEST]))

THEN 'MAT-'+STR(YEAR([PERIODS]))

ELSE 'X'

END

The only limitation I found is that this is only calculating MAT from January to July instead of providing all available months. Let me show you.

I would like also to show from Aug’17 to Dec’17 MAT data, and even Dec’16, as my data starts at January’16.

I guess the limitation comes from defining the Latest Date

But I don’t know how to solve that. Can you help me with that?