Combining Dimension Values in a Single Pivot Cell

[#1]

Hi,

I need to to combine two values in a single pivot cell to show the following :

	Apr	May	Jun	Jul	Aug	Sep
Incomes	4 (7)	8	7	11 (11)	5	2 (4)

The data that supplies this pivot would be set out in the following way :

Income	Apr	4
Income_2	Apr	7
Income	May	8
Income	Jun	7
Income	Jul	11
Income_2	Jul	11
Income	Aug	5
Income	Sep	2
Income_2	Sep	4

So What I basically need to test for is if there is an Income_2 for a month then show it in Brackets in the same cell for the same month.

Many thanks for any help!!

Tom

[#2]

Hi,

Thanks again for your reply.  Ive had a go at your suggestion but with no luck.  Ive attached a more detailed example of my document with the required variables and more datatypes.  Really appreciate it if could show me where I am going wrong.

I've tried your suggestion for months 5 & 6 in the second pivot table.

Regards,

Tom

[jerem1234]

Please find attached. I changed some things up like adding month as second dimension, as well as eliminating all the 'Month' expressions you had to just have one. Then had to use aggr to get the correct values. Also I had to compensate for getting total on the right.

The formula I used was:

=if(count(aggr($(vCalculatedNumber), [Indicator Number], Rolling_Month)) >1 and SecondaryDimensionality()<>0,

concat(aggr($(vCalculatedNumber), [Indicator Number], Rolling_Month), ' (', [Indicator Number]) & ')',

$(vCalculatedNumber))

Please find attached.

Hope this helps!

[#4]

Hi Many thanks for your efforts and help in this.  This is definately what I am after.  The one thing I can see is that some indicators have been concatenated other than the specified ones.  Any idea why?  (eg indicator 531)

One last thing - i need to keep showing the previous rolling 12 months.  Can this be included in the expression ? (Could a rolling_12_month flag be incorporated?)

Once again - thanks for your expertise. Greatly appreciated.

Tom

[jerem1234]

Oops I removed something from the if statement that should have stayed. It should be:

=if(count(aggr($(vCalculatedNumber), [Indicator Number], Rolling_Month)) >1 and SecondaryDimensionality()<>0 and match(min([Indicator Number]), 822, 823, 824, 825),

concat(aggr($(vCalculatedNumber), [Indicator Number], Rolling_Month), ' (', [Indicator Number]) & ')',

$(vCalculatedNumber))

As for the Rolling 12 months, you could use this in your dimension:

=if(date#(month & ' ' & year, 'MMM YYYY') > vMax, date#(month & ' ' & year, 'MMM YYYY'))

where vMax is:

=monthname(AddYears(max(date#(month & ' ' & year, 'MMM YYYY')), -1))

If you are going to do this, I'd create a MonthYear field in your script and then you get to make the 2 formulas above a little cleaner.

PFA

Hope this helps!

[#6]

Perfect!!!!!!! Really really grateful for your help on this.

One really last little things..... if I wanted to show the last rolling 13 months , for example in this case Dec-2012 to Dec 2013 (basically I need to show 13 months worth of data upto the previous month) how could I amend the expression?

Many thanks again!!!

[jerem1234]

To do by 13 months instead of twelve, you'll have to use the addmonths function instead of the addyears. The new expression for the variable vMax would be:

=monthname(AddMonths(max(date#(month & ' ' & year, 'MMM YYYY')), -13))

[#8]

Hi,

Perfect!

Thank you so much for your time!! Most appreciated.

Tom