Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

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

View solution in original post

2 Replies
Highlighted
Partner
Partner

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

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

View solution in original post

Highlighted
Specialist
Specialist

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

Hi, Serhan,

Thanks very much. The second solution works!!!

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

Thanks very much