Mapping in Expression Disables Subtotal

[#1]

Hi guys,

I have created a pivot table which uses the basic sum function. However, my expression also integrates a sign mapping field (created in the script from an excel mapping file), so the expression is: sum(Exp) * ExpSignType. (results in red are negative/black in positive) This results in the table below:

ExpType	Team1	Team2	Team3	Team4	Team5	Team6	Total
A	7,312,710	3,401,868	15,515,633	27,910,919	2,196,218	10,369,605	66,706,953
B	6,380	17,917	1,092,844	1,350,199	91,243	90,788	2,649,371
C	0	0	0	1,383	0	0	1,383
D	6,839	0	15,997	101,572	0	0	124,408
E	0	0	(15,997)	(108,411)	0	0	(124,408)
F	0	0	0	(3,679)	0	0	(3,679)
G	0	(38,587)	0	(120,638)	(31,165)	0	(190,391)
H	(117,220)	(36,111)	(276,027)	(649,683)	(55,559)	(3,936)	(1,138,536)
I	(195,806)	0	(125,411)	(130,548)	(4,272)	0	(456,037)

This gives the correct values but one problem i have is that when I do this i am no longer able to tick the 'partial sums' button and therefore cannot give the subtotal for each team. Does anyone know a way around this? It would be pretty difficult to create the expression in the script so any other ideas ould be appreciated.

Thanks,

[Miguel_Angel_Baeyens]

Hello,

You shouldn't see any difference with

sum(Exp * ExpSignType)

(haven't tested though). Even better, you can create a new field in your load script

Exp * ExpSignType AS DisplayExp,

and use it in the expressions instead of multiplying.

Hope this helps

[#3]

Thanks MIguel,

But changing the parentheses changes the figures quite drastically unfortunately. The expsigntype resides on a separate table to exp which I could join to create the expression but i was hoping there would be another way around it.

[Miguel_Angel_Baeyens]

Hello,

You don't need to join (well, it will depend on the load script performance, but I don't think you need) but I'd use a mapping table to get it on the same table that exp, kind of

ExpSigntypeTableMap:MAPPING LOAD ID, // This is the key field between both tables expsigntypeRESIDENT Table1; Data:LOAD *, ApplyMap('ExpSigntypeTableMap', ID, 1) AS ExpWithSign // This will return the corresponding expsigntype for the ID // "1" would be the default value, is optionalFROM Source;

Hope this helps

[johnw]

Perhaps this?

sum(aggr(sum(Exp)*ExpSignType,ExpType,Team))

[#6]

I think both answers look as though they would work but as I have a number of tables concatenating onto one another I'm going to use the expression approach which works for me.

Thanks both of you for your input, much appreciated!

[#7]

Looks like I'm actually going to have to try Miguel's approach. the performance of the expression seems to make the chart extremely slow unfortunately.