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: 
fred_s
Partner - Creator III
Partner - Creator III

Summarize fieldvalues in Variable

Hi,

Although it looks actually quite simple to me, I can't solve it.

Table example:

Accountnr

Totaldeb

1

6454

3

87452

5

5145

6

12458

10

8514

18

67158

20

2917

21

7534

101

12208

200

48554



Actually there are approx. 1000 accountnrs.

What I would like to achieve is to summarize certain values into a variable, for example:
The sum of Totaldeb for accountnr 1,5,6 and 20 should become variable varBanking.
The sum of Totaldeb for accountnr 3,21 and 101 should become variable varStudy.
Probably with a maximum number of 20 accounts per variable.

QV Users should have the ability to change the range of accountnrs, through a (Comma seperated?) QV Inputfield (/variable)

Any help will be appreciated.


LOAD * INLINE [
Accountnr, Totaldeb
1, 6454
3, 87452
5, 5145
6, 12458
10, 8514
18, 67158
20, 2917
21, 7534
101, 12208
200, 48554
];


1 Solution

Accepted Solutions
Not applicable

The best option is to work with a flag.

Make a extra field with a number from the group the acount belongs

Greetz, dirk

View solution in original post

5 Replies
Not applicable

The best option is to work with a flag.

Make a extra field with a number from the group the acount belongs

Greetz, dirk

fred_s
Partner - Creator III
Partner - Creator III
Author


You pointed me in the right direction.

I'm gonna use an Include file to insert this Flag field (this way people can use their own ranges).
Accountnrs and Flags are not likely to be changed.

LOAD * INLINE [
Accountnr, Accounttype
$(Include=grootboek.inc)
];


Thanks!

Not applicable

I created two Inputfields, inpBanking and inpStudy. I think you need one for every variable you want to use. The values are simple comma separated lists. Then for the variable vBanking you would use:

Sum({<Accountnr = {$(=Only(inpBanking))}>} [Totaldeb])
If you want the variable value to be the same regardless of dimension and selection, use:
Sum({1<Accountnr = {$(=Only(inpBanking))}>} TOTAL [Totaldeb])


The principal would be the same for the other variables. I've attached a sample.

fred_s
Partner - Creator III
Partner - Creator III
Author

Hi NMiller and Dirk,

One problem, Two solutions!
My only problem left is to choose the best one...

Thanks for thinking along

Not applicable

With the group column

Variable value 1 =

=



sum({<Group ={'1'}>} Totaldeb)