2 Replies Latest reply on Sep 3, 2018 6:35 AM by Noud van Heeswijk

# Adding a custom Reference Line

Hi All,

I am very new to the tableau and have been going crazy to figure out this. Here is what I am trying to do:

I have monthly volume for different categories.(I am attaching sample data). I am adding a reference line for Average and Standard Deviation. However the average and standard deviation which is being calculated is based on the whole data. What I want is to add the reference line for average based on last 12 months and excluding current month.

For example: For the below data, the reference line is showing average as 12.16(which is an average of all rows). What I want to show is average reference line as 5.3 which is an average of volume from Mar-17 to Feb-18. Similarly for standard deviation as well.

 Date Product Volume Mar-17 Computer 3 Apr-17 Computer 4 May-17 Computer 5 Jun-17 Computer 6 Jul-17 Computer 7 Aug-17 Computer 8 Sep-17 Computer 9 Oct-17 Computer 3 Nov-17 Computer 4 Dec-17 Computer 5 Jan-18 Computer 6 Feb-18 Computer 4 Mar-18 Computer 100
• ###### 1. Re: Adding a custom Reference Line

Hi Somya,

Find my approach as reference below and stored in attached workbook version 10.2 located in the original thread

1. M1. Last 12 month: if DATEDIFF('month',[Date],today())>1 and DATEDIFF('month',[Date],today())<=14 then [Volume] END

2. Average

Regards,

Norbert

• ###### 2. Re: Adding a custom Reference Line

Hi Norbert,

But I'm also looking for an adjustment.

I hav a Bar-graph over the last six months.

I'd liked to show a constant regference line based on the average of the 12 months prior to the six wich are shown in the graph.

how do I have tot modify you formula?

Kind regards Noud