2 Replies Latest reply: Mar 30, 2015 1:57 PM by Massimo Grossi

# Sum of field based on another field value

Hi,

How to get sum of fields based on another field value.

For Example.

I have Target, Field 1, Field 2 , Field 3 , Field 4

TargetField 1Field 2Field 3Field 4
15678
26789
35765
44544

If Target = 1, then the value should be 5

If Target = 2, then the value should be 6+7

If Target = 3 then the value should be 5+7+6

If Target = 4 then the value should be 4+5+4+4 and so on..

Please let me know how to get this in chart expression. I have 15 fields which i need to sum based on the target value.

• ###### Re: Sum of field based on another field value

Its quite straight forward if you pivot the field headers to become a single dimension.

Using your data you can do this as follows

Temp:

CrossTable(Field, Value)

[Field 1],

[Field 2],

[Field 3],

[Field 4]

FROM

(html, codepage is 1252, embedded labels, table is @1);

Data:

*,

right(Field,1) as FieldNumber

Resident Temp;

drop table Temp;

Then its easy to build an expression in a chart to accomplish this:

• ###### Re: Sum of field based on another field value

a:

[Field 1],

[Field 2],

[Field 3],

[Field 4],

Repeat('1', Target) & Repeat('0', 4 - Target) as b

FROM