Display most recent value based on date selection (island calendar and variable)

José_Espinoza — Sat, 16 Nov 2024 20:19:33 GMT

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.

ID	VALIDITY_START_NUM	VALIDITY_END_NUM	AMOUNT	AMOUNT2
3015080	20171109	20180830	94436	438
3015080	20180827	20180830	78686	438
3015080	20180830	20190831	55005	568
3015080	20190702	20190831	70668	568
3015080	20190831	20200731	85202	454

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.

Re: Display most recent value based on date selection (island calendar and variable)

José_Espinoza — Mon, 05 Aug 2019 13:58:52 GMT

I got one to work, but at the same it doesn't...

min(total aggr(sum( {< VALIDITY_START_NUM={"<$(vDateNumMax)"}>} AMOUNT),VALIDITY_START_NUM))

When I pick an ID it works nicely, but with no selections everything goes to 0.

Re: Display most recent value based on date selection (island calendar and variable)

José_Espinoza — Fri, 09 Aug 2019 16:24:25 GMT

I solved it by the way, the solution was:

(sum(aggr(if(max( {<VALIDITY_START_NUM={"<$(vDateNumMax)"}>} total <ID> VALIDITY_START_NUM)=VALIDITY_START_NUM, AMOUNT),ID,VALIDITY_START_NUM)))

topic Display most recent value based on date selection (island calendar and variable) in QlikView

Display most recent value based on date selection (island calendar and variable)

Re: Display most recent value based on date selection (island calendar and variable)

Re: Display most recent value based on date selection (island calendar and variable)