topic Sum a column conditionally in QlikView

Sum a column conditionally

andre_ficken — Mon, 27 Dec 2010 11:15:16 GMT

Hello, I am trying to get the following: in a graph report I am showing totals per value of PL, there are 0,1 or more GL account totals for 0,1 or more periods linked to it. Depending on de value in de Totals column I would like to populate a cell. So for example the row where the Gtotal value is set to 'T1' (indicating the total row for T1) I would like to populate the Debet column with the total value of debet where the Totals columns = 'T1'.

I used the following formula:

if ((Gtotal = 'T1'), sum({$<Totals={T1}>}acd_dr_amt),sum(acd_dr_amt))

but sofar no luck in achieving what I want. My guess is that I am missing something, but I cannot get my head around what....

I tried to attach the qvw file to this post but got error messages back. So send me a mail a to get the file mailed directly
if you want to have a look at it.

Thanks in advance for your help.

Sum a column conditionally

tresB — Mon, 27 Dec 2010 11:32:50 GMT

Hi Anre,

Instead of If(Condition,Sum()) structure use SUM (IF(Condition)) structure.

Regards, tresesco

Sum a column conditionally

andre_ficken — Mon, 27 Dec 2010 11:47:46 GMT

I understand what you mean, but I need 2 condition tests: condition 1 is the row that holds the total, only for these rows I need to do a sum (Gtotal column value test). The columns that need to be summed hold the appropriate value in the Totals column. When I use the latter part of my posted formula I get what I would expect. The situation that I am trying to resolve is:

When the Gtotal cell for the row holds the value 'T1' if needs to sum the column 'Debet' for all rows where the Totals column holds the value 'T1'.

I hope this makes sense to you.

Sum a column conditionally

tresB — Mon, 27 Dec 2010 13:11:19 GMT

Hi,

It sounds that you need to check two conditions together . Is it something like- Sum(IF(Gtotal='T1' AND Totals='T1',Debet)), that you want? If not, try to upload a sample data set with desired output mentioned.

Regards, tresesco

Sum a column conditionally

andre_ficken — Mon, 27 Dec 2010 13:39:08 GMT

Hi there,

Here is a small example in xls format. The sheet shows my data summed up already.
I want to get the sum of debet for all rows where the column totals is T1 in the cell D5.
That is the cell where Gtotal = T1.

Same criteria go for cell D9 for Totals = T2 and D13 for Totals = T3.

A small extra challenge for D14 where the sum for Totals T2, T3 have to go in.

Off course debet is only the start, credit and saldo will have to follow as well as more complex totals.

Hope this helps.

Sum a column conditionally

tresB — Tue, 28 Dec 2010 06:33:05 GMT

Hi Andre,

Find the attached application. hope this is what you wanted.

regards, tresesco

Sum a column conditionally

andre_ficken — Wed, 05 Jan 2011 09:31:17 GMT

Hi tresesco,

This is indeed the solution. Thanks for your help. I also found that version 10 works differently from 8.52 regarding the datasets. Problems solved now, so I am happy. Thanks again.

André