Cross Currency Data

[jdmarlin]

I have an interesting problem I'm stumped with. I have a data set with four currencies: CAD, USD, EUR, GBP, and I want to be able to select a given currency, for example GBP, and have it show me all associated securities and their respective cross currency.... in this case CAD.

So in data set below I have three GBP securities: B, J, and K, and when I select GBP I want it to show me both the CAD and GBP side of those three securities.

Security	Currency	Amount
A	CAD	1000
A	USD	2000
B	CAD	1000
B	GBP	3000
C	CAD	1000
C	USD	2000
D	CAD	1000
D	USD	2000
E	CAD	1000
E	USD	2000
F	CAD	1000
F	USD	2000
G	CAD	1000
G	EUR	4000
H	CAD	1000
H	EUR	4000
I	CAD	1000
I	USD	2000
J	CAD	1000
J	GBP	3000
K	CAD	1000
K	GBP	3000

Result from selecting GBP:

Security	Currency	Amount
B	CAD	1000
B	GBP	3000
J	CAD	1000
J	GBP	3000
K	CAD	1000
K	GBP	3000

Is this possible in QlikView?

[maxgro · Accepted solution]

try with a straight table chart

2 dimensions

     Security

     Currency

expression

     sum({$ <Security=P(Security),Currency=>} Amount)

[jdmarlin]

And set analysis continues to blow my mind.. thanks!