topic Re: Less/greater than with Set analysis? in QlikView

Less/greater than with Set analysis?

Mon, 18 Aug 2014 19:24:34 GMT

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.

Re: Less/greater than with Set analysis?

Mon, 18 Aug 2014 19:54:05 GMT

Seems good.

Re: Less/greater than with Set analysis?

Mon, 18 Aug 2014 20:07:25 GMT

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>

Re: Less/greater than with Set analysis?

MK_QSL — Mon, 18 Aug 2014 20:18:38 GMT

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])

Re: Less/greater than with Set analysis?

Mon, 18 Aug 2014 20:20:00 GMT

Possible to attach a sample file?

Re: Less/greater than with Set analysis?

jagan — Tue, 19 Aug 2014 02:51:58 GMT

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.

Re: Less/greater than with Set analysis?

Thu, 21 Aug 2014 11:28:30 GMT

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)