Add a expression as a dimmension

[#1]

Hello...

I have to build a table that has only one dimenssion (Year) and several totals collums. The problem I face, If after that, I have to show the difference between this year and prior year.

Something like this

Year (dimm)	Q sold (exp)	Q amount(exp)	Difference between years(exp, one for each cummulative exp)	% dif betwen years (same as last col)
2010	count(records)	Sum(records)	count for 2010 - count for 2011	% of counts
2011

The is a pivot table, and in fact is turned around (dimmesions are on top, expressions are as rows). The last problem is that the user want the last two, showed as collums.

Any idea is this can be accomplish? I try add them as dimensions (no luck) as expression (no luck on how to do the calcs).

Thanks.

[Jason_Michaelides]

Try using Above()

Can you post your app?

[#3]

NO I can't

as you can see the pivot table is inverted. so I have periodo as dimension. I need to add after 201000 another two collumns, that are the diff in the row and the % dif. Is there a way to accomplish it?

Pre, Con, Imp and so on (the rows) are expresions.

[Jason_Michaelides]

There most likely will be a way but it's very difficult to follow exactly what you want.  If you can't post the actual app then can you mock up a test one and detail exactly what you want to see and where?

[#5]

Interesting problem. When you select show partial sums for Year, where does it display?

Edit: Just tested this, and it does do what I expected. However this option only allows you to add 1 additional column and not 2.

If you have show partial sums checked for year, and you have totals on bottom then the totals appear at the right.

You can then use dimensionality to change the expression being used when it is in the total column.

You could even just add 2 new expressions and tell it to only display in the totals and tell the rest to display only in the main part of the table. this will not give you the labels at the top but will then show both expressions on the same row

Year	2009	2010	2011	2012	Other  Expressions
Total Sum of Orders	$25.25	$2,342.00	$2,344.00	$23,424.00
Open Amount	$0.00	$2,324.00	$433.00	$43.00
Total Qty	34	43412	4314	4344
Last Year Order Amount	$0.00	$34,125.00	$3,232.00	$433.00
Current Year Order Amount	$0.00	$0.00	$0.00	$451,222.00
Goal	$0.00	$0.00	$23,413.00	$0.00
Percent of Goal Ach.			0%
Total Cost Shipped	$2,314.00	$342.00	$234.00	$23.00
Invoiced Amt.	$342.00	$0.00	$0.00	$0.00
Margin	55%	0%	0%	0%
New  Expression 1					5465
New  Expression 2					5486

[#6]

LEt's see If I can clarify what I want..

I attached a simple case... there is a pivot table (the one I need) and I added a new one. The new added, should be the same table, and the definition of the field is correct (for What I need).

for each row (that are expression) I need the difference between every column (quan 2010 - quan 2011) and also the dif % (obtaining one will make me achieve the rest I need). I don't need sum, I need diference! this not sound crazy for me, but unless I'm Missing something , you can only achieve an automatic sum of rows? and nothing else?

[sivarajs]

its might helps you.

[danielrozental · Accepted solution]

You should try something like this