6 Replies Latest reply: Jun 21, 2011 5:55 AM by Miguel Angel Baeyens de Arce

# Last Value of a column

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

• ###### Re: Last Value of a column

Hi,

Have a look at the attached document.

Regards,

Kaushik Solanki

• ###### Last Value of a column

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

• ###### Re: Last Value of a column

HI,

Yes

In that case you have to include a set analysis in max.

Hope this will solve your problem.

Regards,

Kaushik Solanki

• ###### Last Value of a column

Thanks Kaushik!!

You gave me enough clue to work around this problem.

Cheers,

Sumit

• ###### Re: Last Value of a column

Hi,

Thats gr8.