Display most recent value based on date selection (island calendar and variable)
Hi people, I hope you can help me with this!
Let's say I have this dataset. In this example, its records share all the same ID because there's a need to keep track of modifications. What I need is to display the latest value on "AMOUNT" based on "VALIDITY_START" for a specific date range. For example, if I go to see the data as of today, it should display "70668" for "AMOUNT". If I go to 2018-01 it should display "94436". If I go to 28-08-2018 it should display "78686", and if I go to 31-08-2018, it should display "55005".
I need to display the latest value AND every other value for the user to keep track of modifications. So imagine this very same table but with a totals over it displaying the result of a sum expression, but this total only shows the most recent value.
Something I must also explain is that this data model and its calculations are based on a single variable called: vDateNumMax. And what does it do? It gets the max date (num format) from an island calendar table. This date is always today's date, but if I pick ie 2018 as year in the date picker, this variable becomes "20181231" and so on for months and days.
I've been trying some expressions with aggr, max and even FirstSortedValue, but I just can't find one to work as I need to...
Any ideas? Thanks beforehand and sorry for the long post, but I wanted to be clear... I'm working on QlikView.