Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
qwebnm
Partner - Creator III
Partner - Creator III

QlikSense how to find closest date from one field to another

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?

SH777_0-1676015100830.png

 

Please help if possible.
thank you

Labels (1)
10 Replies
henrikalmen
Specialist
Specialist

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

qwebnm
Partner - Creator III
Partner - Creator III
Author

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

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
Partner - Creator III
Partner - Creator III
Author

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.

SH777_0-1676276138148.png

please reply if possible

 

henrikalmen
Specialist
Specialist

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

@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
Partner - Creator III
Partner - Creator III
Author

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)

SH777_0-1676524947123.png

 

the final result)

SH777_1-1676525219756.png

 

Please reply if possible!
thank you so much!

 

henrikalmen
Specialist
Specialist

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
Partner - Creator III
Partner - Creator III
Author

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

SH777_0-1676539171665.png
There are many difficulties in solving the problem due to lack of ability.

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

SH777_1-1676539336151.png

 

What should I do, sir?

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