topic Re: Closest Date and return data in QlikView

Closest Date and return data

EMRSD — Wed, 28 Jul 2021 20:39:16 GMT

This should be simple but I'm learning. I want to be able to search the date column for the closest date to today and then return the corresponding value in the total column. Not a sum of all the values up to that date but just the one value. thanks in advance.

Date	Total
07/03/21	336
07/10/21	672
07/17/21	1,007
07/24/21	1,343
07/31/21	1,679
08/07/21	2,015
08/14/21	2,350
08/21/21	2,686
08/28/21	3,022
09/04/21	3,358
09/11/21	3,693
09/18/21	4,029
09/25/21	4,365
10/02/21	4,701

Re: Closest Date and return data

Kushal_Chawda — Wed, 28 Jul 2021 21:51:41 GMT

@EMRSD what will be the preference. closest before today or after today or both?

Re: Closest Date and return data

EMRSD — Wed, 28 Jul 2021 22:05:44 GMT

Before today. TY

Re: Closest Date and return data

Kushal_Chawda — Wed, 28 Jul 2021 23:33:13 GMT

@EMRSD try below. Make sure that your Date field is proper date format.

=sum({<Date ={"$(=date(max({<Date={'=div(Today(1),Date)=1'}>}Date)))"}>} Value )

Re: Closest Date and return data

Kushal_Chawda — Wed, 28 Jul 2021 23:51:26 GMT

@EMRSD also try below in text object and where Date is not present as dimension

=FirstSortedValue(distinct Value , mod(Today(1),Date))

Re: Closest Date and return data

EMRSD — Thu, 29 Jul 2021 13:31:09 GMT

This worked. Perfect. thank you very much!

Re: Closest Date and return data

Kushal_Chawda — Thu, 29 Jul 2021 13:37:23 GMT

@EMRSD Accept solution to appropriate reply