3 Replies Latest reply on Jan 5, 2017 6:33 AM by Simon Runc

# Missing Months and Rolling Sum

I'm trying to show a graph where each month represent the current and last 12 months, and I want to show this over a 12 month period.  I have a lookup filter to correctly filter the view without filtering the data; however, I'm having some issue where I'm not getting the correct values where a company does not have data for every month.  So, in my attached example, The sort field is the value I want to show for the month of November, and that is working correctly for the company All.  However, the company X is only showing the last 12 months for its last data point, which is Sept. 2016.  The sum of amount from 11/1/15 - 10/31/15 (the most recent 12 months) should be \$36,819.  Is there a way to extend the line for another two months and have the value calculated correctly?

Thanks!

• ###### 1. Re: Missing Months and Rolling Sum

Hello Michelle Smith,

Why use the lookup filter instead of a regular date filter on End Month to show the last 12 months?  The values for Company X then match the desired output.  Would there be a need for the values that preceed 12 months in the view?

Patrick

• ###### 2. Re: Missing Months and Rolling Sum

I was using the Lookup filter to filter the view only and not the underlying data.  The each month represents a sum of the rolling 12 months.  So, my understanding was that if you use a regular date filter, then I won't have the values for the previous 11 months I need to be able to do the calculation.

Thanks!

• ###### 3. Re: Missing Months and Rolling Sum

hi Michelle,

How's this for you?

Rather than use a Table Calc filter, I've created a real dimension for what we want to show/hide

[Show/Hide]

IF [End Month] >= #2015-11-01# AND

[End Month] <= #2016-10-31# THEN 1 ELSE 0 END

I make this a dimension (and blue/discrete) and bring into the Colum Shelf...I can then HIDE the '0', which has the same affect as the TC filter (filtering the view and not the underlying data). As we've created the show/hide dimension in this way (and 0 is always hidden) even if the dates (currently hard-coded) were on parameters it would still dynamically update.

Hope that helps/works, and makes sense...but let me know if not