QlikSense how to find closest date from one field to another

[qwebnm]

hello.

Qliksense I would like to know how to find the closest date from one field to another.
I have fields close data, data.
I want to show the date value closest to data in close data.
I don't know what to do.
What should I do?

Please help if possible.
thank you

[henrikalmen]

Could you please explain a little more what it is you want, preferably by giving an example of what the result should be.

[qwebnm]

For example, I want the closest 20221110 to the data date (20221110) to appear in the close data.
Please reply if possible.
Thank you.

[henrikalmen]

I'm sorry, but I still don't understand what you want. Can you draw a table that looks like what you want the result to be?

[qwebnm]

Current date value: 2022-11-10

1) Output the value closest to the current date value (2022-11-10) among closed dates
-> 2022-11-10

2) Alternatively, output the value closest to the date value among the closed dates in the result column.

please reply if possible

[henrikalmen]

Try this, it should give you the date value from the field [closed date] that is nearest to the tested date 2022-11-10.

firstsortedvalue(total [closed date], aggr(fabs(date('2022-11-10')-[closed date]), [closed date]))

You'd need to replace date('2022-11-10') with your actual date value, e.g. today() or a variable or a selected field or what you want it to be.

[henrikalmen]

@qwebnm I see that you clicked "like" on my answer. If the answer is your solution, please mark the answer as the solution for your question so that this topic is marked as closed.

[qwebnm]

Thanks for your help!!!

As you helped me, the close date closest to the date came out in the result!
This has been a huge help!
But I ran into another problem.

I want to give a ditinct because the result values are all the same, but I can't.
Can i know a solution for this?

sript)

date(firstsortedvalue(total [closed date], aggr(fabs([date]-[closed date]), [closed date])))

Problem)

the final result)

Please reply if possible!
thank you so much!

[henrikalmen]

The second column, ”close date”, has different values on each row and therefore you get multiple rows. You need to write a formula that results in only the value you want in that field (so the field close date in the table should probably be a measure, not a dimension). 

[qwebnm]

I didn't understand correctly...
I made test measurements with a script like result.
However, duplicate values keep appearing.

There are many difficulties in solving the problem due to lack of ability.

As a result, I'm hoping it turns out like this.

What should I do, sir?

Please reply if possible.
I would really appreciate your reply!