topic Exchange Rate Value by Year and max selected Month in QlikView

Exchange Rate Value by Year and max selected Month

pljsoftware — Thu, 06 Sep 2012 10:31:01 GMT

Hi all,

I must to have the correct exchange rate by year.

My customer want to select Years and Month, eg. years 2011, 2012 and months Jan, ..., Jun.

By this selections I must to have the exchange rate of max month in selection, so 2011 Jun and 2012 Jun.

How to have it?

I have make an example.

Thanks in advance.

Luca Jonathan Panetta

PLJ Software

Re: Exchange Rate Value by Year and max selected Month

Thu, 06 Sep 2012 13:32:45 GMT

First, I want to clarrify a point - are the columns Month & RateMonth not related?

Because I dont see a realtion between them.

If they are not related - then you have to filter based on RateMonth and not Month (List Box)

If they are related - I have modified the LOAD script to relate them and intoducted a variable to derive the Rate.

Kindly review the attached file.

Re: Exchange Rate Value by Year and max selected Month

pljsoftware — Thu, 06 Sep 2012 14:12:26 GMT

Hi Vigi_baba,

thank you for your answer. The columns Month and RateMonth in my exeample are not related but I can modify the script.

Yor solution its good but I have try with the new excel file and there is a problem.

I have delete the row where date is 1/4/2011 and Company A to try what happens when missing some transaction in a period.

If you reload the new data in the document you can see a null value for the company A in 2011.

Thank you so much for help me.

Luca Jonathan Panetta

PLJ Software

Re: Exchange Rate Value by Year and max selected Month

Thu, 06 Sep 2012 15:07:26 GMT

Hi,

If I understand corrected, 'A' will show null only when 1/6/2011 is deleted.

You can use the below expression:

=Max({<TransactionDate = {"=$(=max(TransactionDate))"}>} Rate)

In this case if 1/6/2011 is not present for 'A' it will take the Rate value for 1/5/2011 while 'B' will take '1/6/2011'.

Re: Exchange Rate Value by Year and max selected Month

pljsoftware — Thu, 06 Sep 2012 15:29:00 GMT

Hi,

I tried your expression but in company A I see the last exchange rate of the last transaction for that company.

I want to have the exchange rate for the max selected month by Currency.

In this case:

selection years 2011, 2012 and months Jan, Feb, Mar, Apr

I want the rate of Apr for 2011 and 2012 but some company have a different Currency (USD, EUR, GBP, YEN,....)

In this case for the selections I must to have

USD 2011 >>> 1,3855

USD 2012 >>> 1,3120

GBP 2011 >>> 0,8605

GBP 2012 >>> 0,8315

Thanks a lot.

Luca Jonathan Panetta

PLJ Software

Re: Exchange Rate Value by Year and max selected Month

Fri, 07 Sep 2012 07:36:13 GMT

Please try this:

=Max(

If(RateMonth = $(=(Max(Month))) and RateYear = Year

, Rate)

)

It works in fine in your initial Qlikview file.

Re: Exchange Rate Value by Year and max selected Month

pljsoftware — Fri, 07 Sep 2012 15:54:09 GMT

Hi,

your answer is correct but I think that my problem is more difficult to solve.

I have try more and I think that my example test isn't as my real document.

Thanks for you help.

Luca Jonathan Panetta

PLJ Software