6 Replies Latest reply: Sep 30, 2014 10:22 AM by Agis Kalogiannis

# Sum by Expression in Chart

Hi All,

I'm in QlikView 11 and I want to display the sum of one column of data in a table chart, but am having trouble with the expression.

Here's a sample of my data:

StatusHours
Open6
Open5
Open8
Open2

Closed

5
Closed6
Closed3

I want to display the total number of open hours

Open Hours
19

Right now I'm trying to do this using a table chart. I can get the sum by status easily enough, but I can't filter for open. Here's the expression I'm using:

Sum({Status = 'Open'} [TOTAL [Hours])

This is the template that pops up for expressions, but I'm lost on how to translate it into what I'm trying to do:

Sum ({[SetExpression][DISTINCT][ALL][TOTAL[<fld{, fld}>]]} expr)

Thanks in advance for any and all help!

• ###### Re: Sum by Expression in Chart

Hi,

Try below code:

Sum({<Status = {'Open'}>} [TOTAL [Hours])

HTH

Sushil

• ###### Re: Sum by Expression in Chart

I think the expression is:

Sum({<Status = {'Open'}>} TOTAL [Hours])

• ###### Re: Sum by Expression in Chart

Thanks guys, that got me to the right value, but it displays the sum of the open hours for all statuses:

StatusHours
Open19
Closed19

How can I suppress this to only show the open status?

Thanks!

• ###### Re: Re: Sum by Expression in Chart

Hi Katherine

You can work in two different ways:

1. By adding a List box for the Status field and add the expression sum(Hours) in it

2. By creating a calculated dimension in a straight table and use the aggr() function, together with the sum(Hours) function.

Please check the attached example

Regards

Agis

• ###### Re: Sum by Expression in Chart

Whoa! My QlikView world just blew open!

"anywhere you see an ellipses (…) in QlikView you can insert an expression."

Here's how to use a text box to display a calculated field:

1. In the "text" section under the general tab, enter your formula:

= num(Sum({<Status = {'F'}>} TOTAL [Total Status Hours]),'#,##0')

Boom! You've got a text box with your calculated field. Gorgeous!

Thank you guys for your help, I would still have been stuck without the formula suggestions!

• ###### Re: Re: Sum by Expression in Chart

Right.

I though you actually wanted to show all the possible status values there in a table.

Glad you found it! :-)