How to create a rolling N-Month History Report via Pivot Table

[DaveDeV]

Hi All,

In putting together an Excel-based Loss Event Logger application, one of the reports required was to display a rolling history of losses by month per loss category.

I wanted to avoid the situation where an operator had to calculate and enter a date range so needed to provide a formula-based automatic range selection for an entered number of months. The two ranges required were:

1) 12-months (A Year-At-A-Glance)
2) 13-months (As for 1) plus an additional month to provide last month to first month comparison

The attached example shows how this was done.

Rolling N-Month Pivot Table.xlsx

In the source data table, an extra column was created in which a formula compares the event date to the date range selected and injects one of two values ("In" or "Out"). This field is then used at Report Filter level to only include events within the required range (12 or 13 whole months).

Some other "nice-to-haves" included:

a) Selectable Excessive Loss Amount highlighting within the Pivot Report
b) Trend lines across the date range per category (achieved using SparkLines)

The user department manager just loves this one...

Best Regards,

Dave

[FDibbins]

Baie mooi, you did a gret job there
(SA ex-pat here)

[DaveDeV]

And a "When-We" on this end...

An ex-pat for how long now?

PS: Do you miss boerie, biltong and Bovril?

[FDibbins]

10yrs so far, so which stopped whining 1st, you or the plane?
Grew up in PE worked in klerksdorp

i was in port alfred a few months ago for my son's wedding, pigged out heavy on billies and braais lol. Looking at your profile, you probably remember ag please daddy??

[DaveDeV]

Jeremy Taylor! - Yeah - I remember both the singer and the song...

Schools in Salisbury as it was then (now Harare); to Cape Town for university, ran out of funds and joined Burroughs in 1969 after realizing that ChemEng was not a career that I wanted for the rest of my life. Moved to Johannesburg in 1980 and still here...

Duncansville? According to Gargle that's ~midway between Pittsburg and Harrisburg - wazzamatta? couldn't decide which? Certainly a climate that is somewhat different to those from this part of the world...

Anyway, I retired from Unisys at the end of Feb 2011 and on 6th March was called back for 6 months on contract by my recently ex-boss and just before the 6 months was up, an ex-customer of mine called to ask if I could help them with ~40 hours of consulting. 14 months later I took a 2-month break before spending this past Feb full-time Mon-Fri with ex-boss PLUS weekends back with customer. This all tapered off by mid-March and I thought a break would be quite welcome but another ex-customer made me an offer I couldn't understand (3 months at 3x what I used to pull down at Unisys).

And now, not quite halfway through the 3 months they're already talking about a 5 month extension and dumping more work on me...

Coming back to the "I retired" bit... WTF is that?

Dave