topic Re: Very basic SUM help needed in QlikView

Very basic SUM help needed

sspe — Wed, 22 Jan 2014 10:04:27 GMT

Hi,

I'm doing some testing of various load scenarios and for that purpose I have added a text box to my application which I just want to show the sum of an Amount field for all the records I load.

I though this should be very simple, but for some reason I can't get it to show the total sum..

The table I load have the fields GeneralLedger_EntryNo and GeneralLedger_Amount so I tried to add an expression like

Sum ( {1 } GeneralLedger_Amount_LCY)

in my text box, but that always only show 0.

I have also tried with

Sum ( {$ } GeneralLedger_Amount_LCY)

and that will give me the sum for the records I select if I add a listbox containing the GenerelLEdger_EntryNo, but I don't want to have to do any selections - I just always want the total sum for all records.

I think this should be a very simple excercise, but apparently there are something I don't get.

Regards

Steen

Re: Very basic SUM help needed

alexandros17 — Wed, 22 Jan 2014 10:06:48 GMT

Sum(Total GeneralLedger_Amount_LCY)

hope it helps

Re: Very basic SUM help needed

Anonymous — Wed, 22 Jan 2014 10:10:56 GMT

Hi Steen,

That sounds strange.

With no selections made, both expressions should give you the same result (which is the sum of all values in GeneralLedger_Amount_LCY).

Is the GeneralLedger_Amount_LCY field somehow not interpreted as numeric values?

Can you provide an example of what the field values look like?

Re: Very basic SUM help needed

Wed, 22 Jan 2014 10:11:02 GMT

Hi,

Use:

Sum(All GeneralLedger_Amount_LCY)

if need total sum always irrespective of selections.

Re: Very basic SUM help needed

Colin-Albert — Wed, 22 Jan 2014 10:14:44 GMT

Are you summing all debits and credits so the sum showing zero is correct?

Normally General Ledger transactions will sum to zero if all transactions are selected, as every transaction should have a reversing amount against a different GL code.

To see a value you would need to select a range of GL codes rather than all GL codes.

Re: Very basic SUM help needed

Anonymous — Wed, 22 Jan 2014 10:16:21 GMT

Good point! Totally didn't think of that.

Re: Very basic SUM help needed

sspe — Wed, 22 Jan 2014 10:34:57 GMT

Hi,

I was actually only selecting subsets of the GL records in order to avoid getting matching debit/credit records but apparently I didn't really succeded in that...:-(. I ended up only selecting the last 5 records and then I got a total that was different from 0.

I think I just got a little confused about the syntax since it kept showing 0 no matter how I changed my load...:-).

Thanks for the help...

/Steen

Re: Very basic SUM help needed

jonathandienst — Wed, 22 Jan 2014 10:36:34 GMT

You could also gross up the numbers (ie sum all the credits and sum all the debits separately). This is what I normally do:

All negative values:

Sum (RangeMax(GeneralLedger_Amount_LCY, 0))

All positive values:

Sum (RangeMin(GeneralLedger_Amount_LCY, 0))

HTH

Jonathan

Re: Very basic SUM help needed

sspe — Wed, 22 Jan 2014 10:43:41 GMT

Hi Jonathan,

Yep - I think you are right that it might be a better option to do something like that. Thanks for the input.

Regards

Steen