New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Not applicable

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

1 Solution

Accepted Solutions
Not applicable

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

6 Replies
Not applicable

Re: Last Value of a column

Hi,

Have a look at the attached document.

Regards,

Kaushik Solanki

Not applicable

Last Value of a column

Thank you very much for trying Kaushik.

Will this work when there are different currencies as well. for ex:

I apologise i should have given the complete problem

Thank you very much for your time.I appreciate it

Cheers,

Sumit

Not applicable

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

Not applicable

Last Value of a column

Thanks Kaushik!!

You gave me enough clue to work around this problem.

Cheers,

Sumit

Not applicable

Hi,

Thats gr8.

Regards,

Kaushik Solanki

Not applicable

Re: Last Value of a column

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.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Community Browser