how to get the sum of a column and save it in a variable

[Anonymous]

Hi, all,

I searched for a while, but did not find a solution.

The situation is like this, we have a straight table as below:

d1| d2 | d3  | e1                             | e2                            | e3        |

total            | sum(v1+v2+v3+v4)  | sum(o1+o2+o3+o4) |sum(v1+v2+v3+v4)/sum(o1+o2+o3+o4)

a  |  m |       | v1                             |  o1                            | v1/o1

b  |  m |       | v2                             |  o2                            | v2/o2

c  |  c  |       | v3                             |  o3                             | v3/o3

d  |  c  |       | v4                             |  o4                             | v4/o4

The expressions to get column e1 are based on the value of d2:

e1 = if (d2='m', expression1, if(d2='c', expression2, 0))

For the table, I have 2 questions:

1. You can see, this expressions  can not be used to get the total of e1, and we can use sum function in Total Mode to get the value.

But my problem is that, I need to use this sum(v1+v2+v3+v4) value to be displayed in other place in the dash, not the table, how can I reuse this value?

I am wondering is it possible to use AGGR function like this:

AGGR( SUM(if (d2='m', expression1, if(d2='c', expression2, 0))), d2).

I tried, but it dose not work, so I do not know whether the usage like above is correct.

2. As you can see, for column e3, in the Total Mode,  there is no function can be used to calculate this data? Is it possible to use an expression to calculate the data? And of course, if we find a solution for my first question, I can add a text object to get the value of total of e3.

Thanks very much and look forward for your feedback.

Zhihong

[Anonymous · Accepted solution]

Hello,

Try something like

sum( total <d2> if(d2=m,e1,if(d2='c',e2)))

OR BETTER

sum({<d2={'m'}>}e1) + sum({<d2={'c'}>}e2)

OR BEST

Creating a new column in script.

load

...

if(d2=m, e1, if(d2=c, e2) as X,

TABLE;

BR

Serhan

[Anonymous]

Hi, Serhan,

Thanks very much. The second solution works!!!

sum({<d2={'m'}>}e1) + sum({<d2={'c'}>}e2)

Thanks very much