Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Can you try this:
=IF(Year(Date) < 2016, Year(Date), Date(YearMonth, 'MMM-YY'))