Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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])