Preferrably in the script and I'm trying to figure out a 12 months min and max for a given date.
Originally, database contained year fields. Data with dates from January 2016 through December 2016 stored values in fields 2017, 2018, and 2019. January, February, and March 2017 had no data populated in field named 2017, but had data for fields 2018, 2019, and 2020.
Instead of having 6 fields (2015-2020), I moved them all into 3, 1 Year Ahead to 3 Years Ahead. Howeve, now using January 1st, 2016 through March 1st, 2017 date range as an exampl, the values for year 2018 were moved to 1 Year Ahead fiel but for dates from last year (Jan - Dec 2016), values from year 2018 are stored in 2 Years Ahead field which creates this problem of me having to reference either 1 or two fields to find a min for a year.
Here's the original database. If it's easier to calculate 12 months min and max for the first available year for a given date in this format of the database, I'll take that as well. I've been hitting a wall with this for the last three days and still can't resolve it. I'm short of just creating a Min and Max fields in a spreadsheet and forgetting trying to write script and expressions to make this work in QlikView. Figured I'd reach out to the community to see if anyone has any suggestions before I turn to Excel.
Yeah I figured I'd simplify it and see if anyone will try to solve this one instead and then I'll work through and translate the fix to the more complex problem. I'll appreciate any suggestion you may come up with.