Sum Expression with Set or IF

[#1]

Hi everyone,

I'm quite new to QV and I'm struggling to get a sum expression to work properly.

The data I have is in two forms of tables:

Group ID	Programme	Location	Status A	Status B	Status C
1111111	X	London	4	0	3
2222222	X	Glasgow	1	0	2
3333333	Y	London	0	5	2

Unique ID	Status	Programme	Location
101	A	X	London
102	B	Y	Glasgow
103	A	X	York
104	B	Z	London
105	C	Z	London

I want to sum/count all statuses A but filter it according to the programme. In the first table it would be to sum up all the fields under Status A while in the second it would could all the As under Status. The expression I used for the first table was this:

Sum({ <Programme = {'X'}>} [Status A])

For the second table I used this expression:

Sum({ <[Programme]={X}>} [Status]='A'))

They give the right result and I can filter by the different dimensions, except for if I try to filter by Programme. When I make a selection in the Programme list box the result stays locked to the Programme=X selection, whether I select X or Y programme.

Is this just a limitation of set expressions? How would I get around this? I tried to figure out an IF statement that would do this as well but couldn't quite get it to work.

Any help would be much appreciated!

[anbu1984]

If you use constrain on Programme = {'X'} in Set analysis, then you wont see results for other Programmes

Can you post sample app.

[#3]

Yeah, that was the intention, but I thought that selecting a different programme would change the result to 0. Instead it just stays locked at the same value. Oh I just realised that "set expression" might set the programme selection permanently?

Sorry, by sample app you mean a sample from the QV file?

[anbu1984]

Yes

[gandalfgray · Accepted solution]

If you want to allow the chart to take into account your "manual" selections for Programme you should use *= instead of =:

<[Programme]*={X}>

(the QlikView syntax coloring wil indicate that the syntax is wrong, but that's a bug. The syntax is correct)

/gg

[#6]

Thank you, that was exactly the problem!

[#7]

Hi,

I Want to use two Condition that,, How to use Sum with or Condition

[anbu1984]

Sum({ <Field1={'val1'}> + <Field2={'val2'}> } Amt )