topic Search for the smallest closest date to the filter in App Development

Search for the smallest closest date to the filter

Dommnn — Thu, 20 Oct 2022 20:17:44 GMT

Hi all!

There are two tables:
1. A loaded table with the dates of changes in the prices of the product:
01.03.2021 100
02.05.2021 120
04/04/2022 130
06.06.2022 160

2. A standard table with a calendar that is used for the filter.

Necessary:
In the interface when setting the filter by date, display the price closest to the filter date (the price that acted on the date of the filter)

Example 1: The value of the filter "05.05.2022" should be displayed the next price to this date: "130" (was set 05.05.2022)
Example 2: The value of the filter "07.03.2021" should be displayed the next price to this date: "100" (was set 03.03.2021)

The loading script is undesirable, better through the interface.
I will be very glad to help

Re: Search for the smallest closest date to the filter

vinieme12 — Fri, 21 Oct 2022 09:25:22 GMT

as below

temp: load Date(date#(date,'MM.DD.YYYY')) as DATE,value inline [ date,value 01.03.2021,100 02.05.2021,120 04.04.2022,130 06.06.2022,160 ]; Calendar: LOAD Date(_DateStart+(Iterno()-1),'$(DateFormat)' ) as [DATE] While (_DateStart+(Iterno()-1)<=_DateStop); LOAD Floor(Min(Fieldvalue('DATE',RecNo()))) as _DateStart, Floor(Max(Fieldvalue('DATE',RecNo()))) as _DateStop AUTOGENERATE FieldValueCount('DATE'); exit Script;

In chart:

Dimensions:

recordid

DATE

Measure:

sum({1}value) // for checking

the below measure will show results for next possible date

sum({<DATE={"$(=Min({< recordid=p({<DATE={">$(=Max(DATE))"}>} recordid) , DATE= >}total DATE))"} >} value)

you can add a condition as below

=if(sum(value),sum(value), sum({<DATE={"$(=Min({< recordid=p({<DATE={">$(=Max(DATE))"}>} recordid) , DATE= >}total DATE))"} >} value) )

Re: Search for the smallest closest date to the filter

Dommnn_2022 — Wed, 26 Oct 2022 18:01:02 GMT

Thanks a lot! it works!

I have one additional task , i have similar table with the name of the goods.
The name changes in time, below is an example.
Accordingly, i need to find out what name the product had at a certain period of time.

i need to know results for previous possible date

For example if value of the filter is "19.07.2021" should be displayed "Name2" (was set at 15.05.2021 and changed in 21.04.2022 )

Table:

17.03.2021 Name1
15.05.2021 Name2
21.04.2022 Name3
18.06.2022 Name4

Re: Search for the smallest closest date to the filter

vinieme12 — Thu, 27 Oct 2022 13:35:53 GMT

just the below changes

sum({<DATE={"$(=Max({< recordid=p({<DATE={"<$(=Max(DATE))"}>} recordid) , DATE= >}total DATE))"} >} value)