Problem with SUM but can set analysis or aggr function fix this?
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.
