6 Replies Latest reply on Oct 5, 2017 9:27 AM by Paul Cawford

# Context filter only shows 30 days - any way to get 6 month average reference line?

I have created a daily report that shows a rolling 30-day bar chart (among a ton of other things). Because there are some days that have no data, and my stakeholders wanted to see a 0 on those dates (vs omitting the day entirely), I created a rolling 30 day calculation and set it as a context filter, with show missing values enabled. It works great.

But now they'd like to see a reference line of a six-month average. I have the calculation correct and it comes up with the correct number when I test it out on a blank sheet...but obviously when I try to use the calc in the viz with the rolling 30 day context filter, it is "blocked" from going back the full 6 months.

Can anyone think of a way around this?

• ###### 1. Re: Context filter only shows 30 days - any way to get 6 month average reference line?

Hi Joe,

One solution is to create a couple of variable that will do the job that you require:

Where [Date] is the date of the event / activity and [Today] is the current date

This first variable returns true if the date is within the last 6 months, false if it is not

[VAR_Last 6 months]

[Date] >= DATEADD('month',-6,[Today]) AND [Date] <= [Today]

Next assuming the value that you are adding to get the sum is named [value] this second part does the magic

[VAR_6 Month Avg]

{FIXED : FLOAT(AVG(IF [VAR_Last 6 Months] THEN [Value] END))}

This returns the average of the value in the last 6 months.  The {Fixed : } with no parameters makes tableau ignore the filters.

The FLOAT is not strictly needed but makes it easier to ensure that results are the same type.  If they are not dual axis cannot be synchronised.

Finally plot the [VAR_6 Month Average] on your chart on the secondary axis and synchronise the axes.  To do this you may need to add FLOAT around your current summed value.

The 'Date' Column must be a continuous value not discrete as otherwise the average line will have breaks.  Please also note that null values will not be included in the average but zero values will.  This could be changed easily by modifying the [VAR_6 Month Avg] variable.

Hopefully this makes sense.  I've attached a sample workbook doing exactly this.  (I've used [VAR_Today] instead of today as this will mean that the example will work regardless of the date)

Regards,

Paul

• ###### 2. Re: Context filter only shows 30 days - any way to get 6 month average reference line?

Thank you for the thorough response. Those are essentially the two calculations I created before, and I can get the average to plot on a brand new worksheet. However, the worksheet I use in my dashboard has a context filter on date, showing just the last 30 days. And where context filters run before Fixed LODs, the calculation doesn't work in this worksheet. It'll plot the line, but it only averages the last 30 days, not the last 6 months.

Any other ideas?

• ###### 3. Re: Context filter only shows 30 days - any way to get 6 month average reference line?

Ah you're right, I'd missed the 'context' part of the filter...

Back to the drawing board

• ###### 4. Re: Context filter only shows 30 days - any way to get 6 month average reference line?

OK so I think the following should work.  It does in my test data

If you can create a new data source from the same data (Data --> New Data Source) and then blend the data by linking through 'Date'  (data --> edit relationships)

You can then create exactly the same variables as in my first suggestion but in the blended data.  Use this new data to create the dual axis and this should work

I have attached a working example.  Hopefully this works for your data source too!

Paul

• ###### 5. Re: Context filter only shows 30 days - any way to get 6 month average reference line?

That worked! I feel like I have sworn off blending since they came out with cross-database joins, and since I got more control over my ETL processes in my new role, so I hadn't thought of that. Great call, and thank you Paul Cawford!

• ###### 6. Re: Context filter only shows 30 days - any way to get 6 month average reference line?

Glad I could be of help!  It was a useful little exercise for me too as I've not had this sort of requirement yet