topic Re: Calculated Dimension in Pivot Table in QlikView

Calculated Dimension in Pivot Table

anthony_kinsell — Thu, 05 May 2016 16:36:38 GMT

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

Re: Calculated Dimension in Pivot Table

Anonymous — Thu, 05 May 2016 16:54:40 GMT

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

Re: Calculated Dimension in Pivot Table

anthony_kinsell — Thu, 05 May 2016 16:59:03 GMT

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

Re: Calculated Dimension in Pivot Table

sunny_talwar — Thu, 05 May 2016 17:01:26 GMT

Can you try this:

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