Sum by Expression in Chart

[#1]

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!

[sushil353]

Hi,

Try below code:

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

HTH

Sushil

[ecolomer]

I think the expression is:

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

[#4]

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!

[Agis-Kalogiannis]

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

[#6 · Accepted solution]

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!

[Agis-Kalogiannis]

Right.

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

Glad you found it! 🙂