Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

2 Replies
JonnyPoole
Employee
Employee

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:

Untitled.png

maxgro
MVP
MVP

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])