topic Re: Sum Expression with Set or IF in QlikView

Sum Expression with Set or IF

Thu, 27 Nov 2014 12:56:46 GMT

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!

Re: Sum Expression with Set or IF

anbu1984 — Thu, 27 Nov 2014 13:45:16 GMT

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

Can you post sample app.

Re: Sum Expression with Set or IF

Thu, 27 Nov 2014 14:06:05 GMT

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?

Re: Sum Expression with Set or IF

anbu1984 — Thu, 27 Nov 2014 14:07:00 GMT

Yes

Re: Sum Expression with Set or IF

gandalfgray — Thu, 27 Nov 2014 14:15:30 GMT

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

Re: Sum Expression with Set or IF

Thu, 27 Nov 2014 14:24:00 GMT

Thank you, that was exactly the problem!

Re: Sum Expression with Set or IF

Mon, 13 Apr 2015 15:00:36 GMT

Hi,

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

Re: Sum Expression with Set or IF

anbu1984 — Tue, 14 Apr 2015 05:07:12 GMT

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