Sum of field based on another field value

[#1]

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

Target	Field 1	Field 2	Field 3	Field 4
1	5	6	7	8
2	6	7	8	9
3	5	7	6	5
4	4	5	4	4

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.

[JonnyPoole]

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)

LOAD Target,

    [Field 1],

    [Field 2],

    [Field 3],

    [Field 4]

FROM

[http://community.qlik.com/thread/158071]

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

Data:

load

  *,

  right(Field,1) as FieldNumber

Resident Temp;

drop table Temp;

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

[maxgro]

a:

LOAD Target,

     [Field 1],

     [Field 2],

     [Field 3],

     [Field 4],

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

FROM

[http://community.qlik.com/thread/158071]

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

add expression to your chart

rangesum(mid(b,1,1)  * [Field 1], mid(b,2,1)  * [Field 2], mid(b,3,1)  * [Field 3], mid(b,4,1)  * [Field 4])