Qlik Community

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
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:

     

PropertyPropertyRefFundDescFundHeading2012201320142015Jan-16Feb-16
4th FloorFAJ/BLKD04Development CostsRunning Costs------
Electrical Installation2nd fix Electrical------
Internal FinishesCarpentry------
Internal FinishesFireproofing------
Internal FinishesInternal Flooring------
Internal FinishesInternal Painting------
Internal FinishesInternal Plastering------
Internal FinishesSub Flooring------
Internal FinishesTiling------
Mechanical Installation2nd fix plumbing------
PreliminariesAccommodations------
PreliminariesCleaning / 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
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

View solution in original post

3 Replies
Highlighted
Creator III
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
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

View solution in original post

Highlighted

Can you try this:

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