Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Partner
Partner

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.

IDVALIDITY_START_NUMVALIDITY_END_NUMAMOUNTAMOUNT2
3015080201711092018083094436438
3015080201808272018083078686438
3015080201808302019083155005568
3015080201907022019083170668568
3015080201908312020073185202454

 

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.

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

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)))

View solution in original post

2 Replies
Highlighted
Partner
Partner

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

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.

Highlighted
Partner
Partner

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

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)))

View solution in original post