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

[jerem1234 · Accepted solution]

Please find attached. If your data model is a little different, then you might have to change up the expression. Used ='Incomes' as a dimension along with Month. Then for the expression I used:

if(count(Income)=1, Income, concat(Income, ' (') & ')')

Hope this helps!

[#3]

Excellent - many thanks for your help!

[#4]

Hope you dont mind me asking another question?  If, more indicators are added to the data in future is it possibly to amend the expression based on an Indicator_ID ? eg :

Ind_No	Indicator	Month	Number
1	Income	Apr	4
2	Income_2	Apr	7
1	Income	May	8
1	Income	Jun	7
1	Income	Jul	11
2	Income_2	Jul	11
1	Income	Aug	5
1	Income	Sep	2
2	Income_2	Sep	4
3	Sales	Apr	45
3	Sales	May	32
3	Sales	Jun	22
3	Sales	Jul	56
3	Sales	Aug	24
3	Sales	Sep	65

to show

Indicator	Apr	May	Jun	Jul	Aug	Sep
Income	4 (7)	8	7	11 (11)	5	2 (4)
Sales	45	32	22	56	24	65
Grand Total	56	40	29	78	29	71

Many thanks again for  your help!

Tom

[jerem1234]

Added the extra requirements, please find attached.

Hope this helps!

[#6]

Outstanding - much appreciated!

Tom

[#7]

Hi again,

This solution was working perfectly until the requirement was changed.  The source data now has many more indicators, so rather then combine values for Indicators 1 and 2, I have added a concat_flag field to the data.

I've attached a sample doc based on your original solution with test data loaded and an example of what I need to show.

Many thanks for your time.

Regards,

Tom

[jerem1234]

Please find attached.

Hope this helps!

[#9]

HI,

Thanks again for your help - works perfectly. 

I'm now trying to include this solution in some other dashboards that I have.  One which is proving difficult is where my pivot expression is already being calculated in a variable.  My pivot expression is set to =$(vCalculatedNumber)

Variable for vCalculatedNumber :

IF(Value=1,

sum([Number]),

//%

IF(Value=2,

num(sum(Number)/sum([Line Total]),'#,##0.0%'),

//rate

IF(Value=3,

Round(sum(Number)/sum([Line Total]), '0.1'),

IF(Value=5,

num(sum(Number)/sum([Line Total]),'#,###.0'),

IF(Value=6,

num(sum([Number])/Pop_Count,'0.0000')

))))

there are also a few hundred indicators and I only need to concatenate about 6 to give three combined indicators.  Can your solution be applied to this too??

Many thanks again for your time.

Tom

[jerem1234]

You should be able to. Try this for dimension:

=if(match(Ind_No_X, 1, 2), 'Income', if(match(Ind_No_X, 8, 9), 'Discounts',Indicator_X))

This matches the 2 indicators you need to combine into one dimension. Should continue with if statements for whichever indicators you need to combine and have the field that lists the name of indicator last for everything else.

As for expression, if you don't need to do any totals, then this expression should work:

=if(not match(Ind_No_X,1,2,8,9), $(=vCalculatedNumber), concat($(=vCalculatedNumber), ' (',-right(Indicator_X, 1)) & ')')

where 1,2,8,9 are all the fields you are trying to combine.

Try playing around with the formula and see if it gets you anywhere if it doesn't work.

Hope this helps!