Problem with SUM but can set analysis or aggr function fix this?

Wed, 24 Feb 2010 12:31:54 GMT

Hi All,

I'm very new to Qlik so apologies if I'm asking something silly!

I'm trying to analyse some data, though am having problems getting the chart to calculate at the correct level.

What I'm trying to do is a little more coplex but simplified I have two tables, as follows:


Table1:
contract	type	limit
a001	X	1000
a002	Y	1000
a003	Z	1500


Table2:
contract	product	amount
a001	AB	100
a001	CD	100
a001	EF	100
a001	EF	100
a002	GH	100
a002	GH	100

Table1 has unique contract numbers, Table2 can have multiple instances of the contract number.

I've created a variable that I'm using as a calculated dimension like this:

$(category) =
if( type='X' and match(product,'EF','CD')>0, 'XEF' , 'Other')

which correctly gives two values in the dimension.

However when I try to sum the 'amount' from Table2 with the 'limit' from Table1 the 'limit' amount shows in each dimension for contract 'a001' as this has a value in both products mapping to XEF and one that maps to 'Other'.

In this example what I'm trying to get to is a chart where:

category	limit		amount
XEF	1000		300
Other	2500		300

I'm guessing I need to use some sort of set analysis or maybe the aggr function, but had a play with these and couldn't get either to give me the result I'm looking for.

Hope this makes sense and any pointers in the right direction would be much appreciated.

Many thanks & kind regards,
Phil

topic Problem with SUM but can set analysis or aggr function fix this? in QlikView