Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlikview Gurus,
I am struggling with an issue and i thought it might be a good idea to get your point of view on this.
Following is the problem
%XRateID | CUSIP | Procmonth | ClassDescription | Issuer | Ccy | =GBPRate_ME | =Sum(SettAmt*IMVal) |
201009_USD | ABC | 201009 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.583454366 | 129881.1 |
201010_USD | ABC | 201010 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.58829808 | 648409.01 |
201011_USD | ABC | 201011 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.552001717 | 1110524.86 |
201012_USD | ABC | 201012 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.553000528 | -954556.55 |
201101_USD | ABC | 201101 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.58630239 | 109677.54 |
201102_USD | ABC | 201102 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.619396693 | 3032916.41 |
201103_USD | ABC | 201103 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.601096751 | -1496620.16 |
The above table is chart type
The last column "Sum(SettAmt*IMVal)" is an expression.
What i want to do is get and equivalent GBP amount for Sum(SettAmt*IMVal) column using the rate of exchange mentioned in "GBPRate_ME" column.
But the challenge is i need to use the latest rate of exchange for this conversion i.e the Rate of exchange associated with 2011_03_USD row(%XRateID) i.e 1.601096751 for all the conversions. I have highligted this value in RED.
So that resulted grid looks like this
%XRateID | CUSIP | Procmonth | ClassDescription | Issuer | Ccy | =GBPRate_ME | =Sum(SettAmt*IMVal) | Converted Amount @ 1.601096751 |
201009_USD | ABC | 201009 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.583454366 | 129881.1 | 207952.2072 |
201010_USD | ABC | 201010 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.58829808 | 648409.01 | 1038165.559 |
201011_USD | ABC | 201011 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.552001717 | 1110524.86 | 1778057.745 |
201012_USD | ABC | 201012 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.553000528 | -954556.55 | -1528337.391 |
201101_USD | ABC | 201101 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.58630239 | 109677.54 | 175604.353 |
201102_USD | ABC | 201102 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.619396693 | 3032916.41 | 4855992.61 |
201103_USD | ABC | 201103 | CASH & CASH EQUIVALENTS | XYZ | USD | 1.601096751 | -1496620.16 | -2396233.676 |
Please let me know if the problem statement is not clear
I am grateful for your time on this.
Thanks
Regards,
Sumit
HI,
Yes
In that case you have to include a set analysis in max.
max({Ccy = {"CAD"}}GBPrate_ME)
Hope this will solve your problem.
Regards,
Kaushik Solanki
Hi,
Have a look at the attached document.
Hope this will help you.
Regards,
Kaushik Solanki
Thank you very much for trying Kaushik.
Will this work when there are different currencies as well. for ex:
%XRateID | Ccy | ClassDescription | Procmonth | Issuer | CUSIP | =GBPRate_ME | sum(GBPRate_ME) | Variable1 * 10 |
11.08355053 | 16.01097 | |||||||
201009_USD | USD | CASH & CASH EQUIVALENTS | 201009 | XYZ | ABC | 1.583454366 | 1.583454366 | 16.010968 |
201010_USD | USD | CASH & CASH EQUIVALENTS | 201010 | XYZ | ABC | 1.58829808 | 1.58829808 | 16.010968 |
201011_USD | USD | CASH & CASH EQUIVALENTS | 201011 | XYZ | ABC | 1.552001717 | 1.552001717 | 16.010968 |
201012_USD | USD | CASH & CASH EQUIVALENTS | 201012 | XYZ | ABC | 1.553000528 | 1.553000528 | 16.010968 |
201101_USD | USD | CASH & CASH EQUIVALENTS | 201101 | XYZ | ABC | 1.58630239 | 1.58630239 | 16.010968 |
201102_USD | USD | CASH & CASH EQUIVALENTS | 201102 | XYZ | ABC | 1.619396693 | 1.619396693 | 16.010968 |
201103_USD | USD | CASH & CASH EQUIVALENTS | 201103 | XYZ | ABC | 1.601096751 | 1.601096751 | 16.010968 |
201009_CAD | CAD | CASH & CASH EQUIVALENTS | 201009 | XYZ | ABC | 1.49 | ||
201010_CAD | CAD | CASH & CASH EQUIVALENTS | 201010 | XYZ | ABC | 1.54 | ||
201011_CAD | CAD | CASH & CASH EQUIVALENTS | 201011 | XYZ | ABC | 1.84 | ||
201012_CAD | CAD | CASH & CASH EQUIVALENTS | 201012 | XYZ | ABC | 1.553000528 | ||
201101_CAD | CAD | CASH & CASH EQUIVALENTS | 201101 | XYZ | ABC | 1.58630239 | ||
201102_CAD | CAD | CASH & CASH EQUIVALENTS | 201102 | XYZ | ABC | 1.619396693 | ||
201103_CAD | CAD | CASH & CASH EQUIVALENTS | 201103 | XYZ | ABC | 1.8925 |
I apologise i should have given the complete problem
Thank you very much for your time.I appreciate it
Cheers,
Sumit
HI,
Yes
In that case you have to include a set analysis in max.
max({Ccy = {"CAD"}}GBPrate_ME)
Hope this will solve your problem.
Regards,
Kaushik Solanki
Thanks Kaushik!!
You gave me enough clue to work around this problem.
Cheers,
Sumit
Hi,
Thats gr8.
if you got the answer can you please mark this post as answered.
Regards,
Kaushik Solanki
Hi,
For what it's worth and to help further readers to get the proper result, the syntax shoud be
max({< Ccy = {"CAD"} >} GBPrate_ME)
Regards.
BI Consultant