Calculated Dimension in Pivot Table

[anthony_kinsell]

Hi,

I’m trying to create a pivot table of expenditure over a range of properties.

I want to show it by year if it’s before 2016 but by month for 2016 – so something like the below:

Property	PropertyRef	FundDesc	FundHeading	2012	2013	2014	2015	Jan-16	Feb-16
4th Floor	FAJ/BLKD04	Development Costs	Running Costs	-	-	-	-	-	-
		Electrical Installation	2nd fix Electrical	-	-	-	-	-	-
		Internal Finishes	Carpentry	-	-	-	-	-	-
		Internal Finishes	Fireproofing	-	-	-	-	-	-
		Internal Finishes	Internal Flooring	-	-	-	-	-	-
		Internal Finishes	Internal Painting	-	-	-	-	-	-
		Internal Finishes	Internal Plastering	-	-	-	-	-	-
		Internal Finishes	Sub Flooring	-	-	-	-	-	-
		Internal Finishes	Tiling	-	-	-	-	-	-
		Mechanical Installation	2nd fix plumbing	-	-	-	-	-	-
		Preliminaries	Accommodations	-	-	-	-	-	-
		Preliminaries	Cleaning / Waste Disposal	-	-	-	-	-	-

I’ve tried to use a conditional formula in the dimention but can’t get it right – can you suggest an alternative?

=IF(Date < 31/12/15 , Year(Date) , Date(YearMonth, 'MMM-YY'))

I also need to get the order of the years correct.

Regards

[Anonymous]

Hello, Anthony.

Why don't you use both Year and Month as horizontal dimensions but keep all the years "closed" and only 2016 "open" to show the months? That would be the simplest and most straight forward way do achieve what you wand.

If that doesn't work for you, you could use an expression like yours in the script to create a new field for the pivot table:

if(Date <= MakeDate(Year(Today())-1, 12, 31), Year(Date), MonthName(Date)) as YearMonthPivot

Hope it helps.

-Bruno

[anthony_kinsell · Accepted solution]

Hi Bruna,

Thanks for the suggestion - I may try that also.

My formula was just missing some inverted commas - it works now:

=IF(Date <= '31/12/12' , 'Pre 2012', IF(Date <= '31/12/15' , Year(Date) , Date(YearMonth, 'MMM-YY')))

Anthony

[sunny_talwar]

Can you try this:

=IF(Year(Date) < 2016, Year(Date), Date(YearMonth, 'MMM-YY'))