QS Show last value from a unrelated date

JohnDoe — Sat, 16 Nov 2024 18:50:39 GMT

Hello everyone.

I have a Qlik Sense App that must display a few indicators bringing the user the possibility of narrowing data across date dimensions as Year and Month (forget about days).

This app counts with a Master Calendar that is taking from the oldest to the newest date from the Date field from Facts table.

Each indicator has a different source, this means that usually indicators could not share same date ranges, like in the example below:

KPI	Date (DD/MM/YYYY)	Value
1	19/12/2019	3
1	20/12/2019	4
1	14/02/2020	5
2	09/11/2019	6
2	09/03/2020	7
2	01/04/2020	8

The main mission is to display last value from the last date available of each indicator, even though the selected date could be more recent than available date.

The desirable output would become like this examples:

1st desirable case, selected dates: April 2020 or year 2020

KPI	Date (DD/MM/YYYY)	Value
1	14/02/2020	5
2	01/04/2020	8

2nd desirable case, selected dates: December 2019 or year 2019

KPI	Date (DD/MM/YYYY)	Value
1	20/12/2019	4
2	09/11/2019	6

What I wish to avoid is to accumulate data values in the script, I mean to avoid replicate the old value from February to March and April for KPI 1. Copying last recent data in the missing dates on each KPI is an option I want to avoid.

My intended approach is to solve it using expressions, set analysis or flag use on script. I've already played with P() and E() functions without any success.

Fortunately, each indicator can have its own expression, so each KPI could have different set analysis. I'm pretty sure someone else has had the same problem, but I couldn't find on the Community a post related to a similar issue.

Stay healthy, and thank you in beforehand,

Regards,

Re: QS Show last value from a unrelated date

sunny_talwar — Fri, 03 Apr 2020 12:02:54 GMT

Try this

FirstSortedValue({<Date = {"<=$(=Date(Max(Date), 'DD/MM/YYYY'))"}>} Value, -Date)

Re: QS Show last value from a unrelated date

sunny_talwar — Fri, 03 Apr 2020 12:13:18 GMT

I have attached a sample qvf file for you to look at

Re: QS Show last value from a unrelated date

JohnDoe — Fri, 03 Apr 2020 12:37:24 GMT

This worked f l a w l e s s. Thank you very much!!

topic Re: QS Show last value from a unrelated date in App Development

QS Show last value from a unrelated date

Re: QS Show last value from a unrelated date

Re: QS Show last value from a unrelated date

Re: QS Show last value from a unrelated date