# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Creator

## Calculated Dimension in Pivot Table

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

1 Solution

Accepted Solutions
Highlighted
Creator

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

3 Replies
Highlighted
Creator III

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

Highlighted
Creator

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

Highlighted
MVP

Can you try this:

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