Pivot Table

[#1]

Hi,

I have two pivot tables. The dimension of both the pivot tables are different and so, I am unable to combine it into single table. I have kept the two tables closely, so that it appears as a single table. But, if my month is updated (new month added), the 1st table expands. Is it possible to move the second table in accordance with 1st table. Is some macro has to be written for that. Please see the attached file.

[#2 · Accepted solution]

It would be a lot easier to just get rid of the second table. You can use Secondary Dimensionality and Partial Sums to create a different expression at the end.

SecondaryDimensionality() is going to return a Null for the Partial Sum on your Month field. So you can use an if...then to load your Table 2 expression when SecondaryDimensionality is not greater than 0.

=If(SecondaryDimensionality()>0, sum(if(Code='TK', Amount)), if(Code1='TP', (Amount1)))

Then just make sure you set up Partial Sums (Presentation tab) on your Month field. You can even change the name on the Total field to Target on that same tab.

Look at the attachment.

[#3]

Wonderful NMiller. It works perfectly. Thanks a lot.

[#4]

Hi, Is it possible to have multiple columns in the pivot table with that condition. See the attachment

[#5]

I don't see what you're trying to do there. The If function works like this:

If(CONDITION, RESULT IF TRUE, RESULT IF FALSE)

You can't put another comma and expression after the false. You can nest them though. I changed your expression to:

=If(SecondaryDimensionality()>0, sum(if(Code='TK', Amount)),
if(Code1='TP', (Amount1), if(Code2='TU', (Amount2))))

That expression is valid, but it gives the same result as my original. It's saying: In the total column, if Code1 = TP then display Amount1 Else if Code2=TU (and Code1 <> TP) then display Amount2.

What are you trying to do there? You won't be able to get more than one Total column in this situation.

[#6]

Ok. In my case, I need to add target for 2 years (2 columns) and also a calculated colums in the expression. What do you think a good solution for mycase.

[#7]

So, you need to add another expression? The number of columns in the Total area has to match the number regular columns. If you have two expressions, you can have two total fields.

The worst option (but usable) would be to make each month across the top a separate expression and then you can add the Totals as their own expressions and add as many as you want.

Another option would be to try and load the Totals in as records in your data. Instead of a month dimension, these records would have Target1 or Target2.

If you can find a way to use the same number of expressions as Total columns you need, then that would be ideal.

[#8]

I have already tried the way you suggested. I made a column for every month and add other columns as well. This method works, but I am unable to make it dynamic. i.e., since the month column is hard-coded, I can't find a way to move as the month data comes. If you see the attachment, I have hard coded months. Suppose if Jun 2009 data comes, I need to manually adda column. How to make it dynamic?

[#9]

You can make them dynamic based on the Max(Date). Max(Date) currently comes out as May 2009. You should also be using Set Analysis as it is faster than the if...then.

Here's what you'd use for the most recent month:

Sum({<Month={"$(=Date(Max(Month), 'MMM YYYY'))"}, Code={'TK'}>}Amount)

Then for the previous, you could use:

Sum({<Month={"$(=Date(AddMonths(Max(Month), -1), 'MMM YYYY'))"}, Code={'TK'}>}Amount)

And so on. Then for the title, use:

=Date(Max(Month), 'MMM YYYY')

And for the previous months use:

=Date(AddMonths(Max(Month), -1), 'MMM YYYY')

And so on. You should also consider selections as the Max function returns the Max of the selected values if a selection is made on that field.