topic Re: Re: Sum by Expression in Chart in Qlik Learning Discussions

Sum by Expression in Chart

Tue, 30 Sep 2014 02:31:18 GMT

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:

Status	Hours
Open	6
Open	5
Open	8
Open	2
Closed	5
Closed	6
Closed	3

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

sushil353 — Tue, 30 Sep 2014 04:43:12 GMT

Hi,

Try below code:

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

HTH

Sushil

Re: Sum by Expression in Chart

ecolomer — Tue, 30 Sep 2014 04:48:09 GMT

I think the expression is:

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

Re: Sum by Expression in Chart

Tue, 30 Sep 2014 12:13:43 GMT

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

Status	Hours
Open	19
Closed	19

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

Thanks!

Re: Re: Sum by Expression in Chart

Agis-Kalogiannis — Tue, 30 Sep 2014 12:26:26 GMT

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

Tue, 30 Sep 2014 12:42:04 GMT

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

Agis-Kalogiannis — Tue, 30 Sep 2014 14:22:01 GMT

Right.

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

Glad you found it! 🙂