topic Summarize fieldvalues in Variable in QlikView

Summarize fieldvalues in Variable

fred_s — Thu, 21 Oct 2010 14:13:58 GMT

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

Summarize fieldvalues in Variable

Thu, 21 Oct 2010 14:23:12 GMT

The best option is to work with a flag.

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

Greetz, dirk

Summarize fieldvalues in Variable

fred_s — Thu, 21 Oct 2010 14:50:45 GMT

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!

Summarize fieldvalues in Variable

Thu, 21 Oct 2010 14:52:56 GMT

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.

Summarize fieldvalues in Variable

fred_s — Thu, 21 Oct 2010 15:04:59 GMT

Hi NMiller and Dirk,

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

Thanks for thinking along

Summarize fieldvalues in Variable

Thu, 21 Oct 2010 15:09:39 GMT

With the group column

Variable value 1 =

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