Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please try this:
=Max(
If(RateMonth = $(=(Max(Month))) and RateYear = Year
, Rate)
)
It works in fine in your initial Qlikview file.
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.
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
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'.
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
Please try this:
=Max(
If(RateMonth = $(=(Max(Month))) and RateYear = Year
, Rate)
)
It works in fine in your initial Qlikview file.
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