Totals for most recent years in a pivot table

[hansdevr]

I want to make a pivot table which shows totals for 4 categories, within a range of years. At the top of the table, I want a total for the years before 2005 (or another year of choice).

fields are year, category, amount.

Example:

year	category	1	2	3	4
<2005 / other		€ 170.000	€ 62.000	€ 33.000	€ 25.000
2005		€ 10.000	€ 7.500	€ 6.000	€              -
2006		€ 12.000	€ 1.000	€ 5.800	€              -
2007		€ 13.000	€ 800	€ 5.700	€ 100
2008		€ 27.000	€ 3.000	€ 5.600	€ 500
2009		€ 27.500	€ 3.500	€ 5.500	€ 400
2010		€ 28.000	€ 4.000	€ 5.700	€ 600
2011		€ 29.000	€ 5.000	€ 5.800	€ 800
2012		€ 30.000	€ 3.400	€ 3.400	€ 750
2013		€ 28.000	€ 3.500	€ 2.800	€ 900
2014		€ 34.000	€ 3.600	€ 2.900	€ 1.000
2015		€ 50.000	€ 4.000	€ 3.100	€ 1.050
2016		€ 60.000	€ 4.100	€ 3.700	€ 1.100

I saw that dimension limits in a pivot table is not possible. How should I go about achieving this?

Any help would be very welcome!

[sunny_talwar]

May be use a calculated dimension:

If(Year < 2005, '<2005/other', Year)

[Anonymous]

did you try with calculated Dimension?

if (year >= 2005, year, '<2005 / other')

[hansdevr]

Hmm, both answers could be right, I will try.. I was thinking along the path of set analysis, using rank() and / or aggr() in the sum() of amount...

[sunny_talwar]

Well using set analysis you would be able to restrict data, but i don't see how you would combine data into buckets using set analysis.

[hansdevr]

You have a point. So, I should indeed fiddle around with a calculated dimension. Suppose I would like to show only the most recent ten years and the rest as "other".. How shoud I do that?

[sunny_talwar]

Calculated dimension or you can may be create a new field in the script:

LOAD Year,

          If(Year < 2005, Dual('<2005/other', 2004), Year) as NewYearDimension

FROM ....

and now you can use NewYearDimension as your chart dimension

[sunny_talwar · Accepted solution]

May be this as calculated dimension:

If(Year < Year(Today()) - 10, 'Other', Year)

[hansdevr]

No, I need to still have all those years in other charts...

[sunny_talwar]

You will still have the Year dimension to use wherever you want to use. You are just creating a new field for this particular chart. No information is getting lost