Hello everyone,
I'm trying to find a max value within a constantly moving array. The idea is very simple, I have a database with two fields - Date and Exchange Rate - and, for each row of data, I'm trying to find a highest and lowest exchange rate over the last year.
For example, I have an exchange rate for each day over the period of two years starting on Jan 1, 2015 and ending Dec 31, 2016. I would like to create a field in the script that would:
- Look at every date on each row of data
- Find a date one year from date on current row
- Search through an array of dates that is between date that is on specific row and date that is a year ago from date on a row
- Find max exchange rate for an array identified in point 3
Continuing with the example, on Jan 1, 2015, max exchange rate would be the exchange rate for Jan 1, 2015. For 2nd line of data that has an exchange rate for Jan 2, 2015, QlikView would look at an array of two dates and corresponding exchange rates and select the max exchange rate between Jan 1, 2015 and Jan 2, 2015. For the line of data that has, say Mar 14, 2016 date, it would look for an array of data from Mar 15, 2015 through Mar 14, 2016 and pick the max exchange rate (which should be 0.952744) for that one year period.
Attached is spreadsheet with exchange rates and QVW that I'm working on. In the QVW I'm trying to find a way to populate the 12_Months_High and 12_Months_Low fields.
Any help will be much much appreciated! Thanks in advance Qlik Community!
Mikhail Bespartochnyy
