Less/greater than with Set analysis?

[#1]

How to do this in set analysis? I want to display average costs of a group (based on ammount of citizens) where the ammount if citizens is less than or greater than 100.000. As an average line in a barchart.

Wat I've done so far:

=sum( {$ <Organization={"=sum([Ammount citizens])>100000"} >} [Cost A]) + sum( {$ <Organization={"=sum([Ammount citizens])>100000"} >} [Cost B])

/

sum( {$ <Organization={"=sum([Ammount citizens])>100000"} >} [Ammount citizens])

'Organization' and 'Ammount citizens' are the dimensions.

[#2]

Seems good.

[#3]

But it isn't working, I don't get the same outcome when calculated without

Qlikview.

Op 18 aug. 2014 21:54 schreef "Ajay Prabhakaran" <qcwebmaster@qlikview.com>:

Qlik Community <http://community.qlik.com/> Less/greater than

with Set analysis?

reply from Ajay Prabhakaran

<http://community.qlik.com/people/AjayVallab?et=watches.email.thread> in *New

to QlikView* - View the full discussion

<http://community.qlik.com/message/590622?et=watches.email.thread#590622>

[MK_QSL]

Try

=(sum( {$ <Organization={"=sum([Ammount citizens])>100000"} >} [Cost A]) + sum( {$ <Organization={"=sum([Ammount citizens])>100000"} >} [Cost B]))

/

sum( {$ <Organization={"=sum([Ammount citizens])>100000"} >} [Ammount citizens])

[#5]

Possible to attach a sample file?

[jagan]

Hi Wilfred,

Why you are using Ammount citizens as dimension it seems like a measure, I think your expression is correct, but Ammount citizens dimension seems to be an issue, can you remove and check.  If not working then attach some sample data and expected output.

Regards,

Jagan.

[#7]

You're right about the dimension 'citizens', this is indeed not a dimesion (but a value). Attached an example of the data (and the calculation in Excel).

Organization	Ammount citizens	Cost A	Cost B	Cost per citizen	Cost per citizen
A	80.000	2000000	3000000	€                62,50	Group '< 100.000'	€    65,27
D	750.000	40000000	2000000	€                56,00	Group '> 100.000'	€    53,70
C	44.000	900000	1240000	€                48,64
D	120.000	4000000	9000000	€              108,33
E	85.000	2750000	950000	€                43,53
F	300.000	9500000	9500000	€                63,33
G	25.000	850000	875000	€                69,00
H	110.000	6540000	3000000	€                86,73

Group '< 100.000' =SUM(C2:D2;C4:D4;C6:D6;C8:D8)/SUM(B2;B4;B6;B8)

Group '> 100.000' =SUM(C3:D3;C5:D5;C7:D7;C9:D9)/SUM(B3;B5;B7;B9)