Column Dependency

[vancet]

Hey all.  I am needing to compare 2 date fields to get average days lapsed in between separate services.  Example - patient comes in on 3/2/2025 (1st Service Date) for intake (1st Service Code), and then comes in on 3/8/2025 (2nd Service Date) for follow up (2nd Service Code).  I am wanting (1st Service Code) to filter out results with only that code, and then then display the date of that service.  Then I am wanting the 2nd Service Code column to only show when the follow up code happened for that same patient and display that date.  All columns would be based on distinct patient id numbers.  Screenshot below of what I have currently, just not sure how to build the dependency.  Thanks for your help.

[marcus_sommer]

I think you could use interrecord-functions like previous() and peek() within n properly sorted resident-loads to query the current record against the previous one to create n flags, continuous counter backwards and/or forwards and/or the min/max ones and/or any kind of offsets between them. 

[vancet]

I'm going to need that explained a little more plainly.  I'm not following how that would play out.

[marcus_sommer]

I couldn't give more detailed suggestions because it's not quite clear which information with which logic should be compared unless that you mentioned to compare the order of visits over multiple records.

Therefore the general recommendation to use interrecord-functions. Ideally you didn't try to apply them at first within the origin application else using a dummy-app with maybe one or two dozens records and then just testing the results of previous(MyField) with this sorting and then another one and then embedding them within n if-loops to perform the comparison. By using peek() instead of previous() you could also access the records from a new created field. Take also a look within the help to these functions.

[vancet]

I am needing to compare the date columns to show number of days lapsed between visit 1 and visit 2.

I'm sorry, I'm very new to Qlik and trying to learn as I go.

[marcus_sommer]

A date-offset could be easily calculated in the way of:

DateX - DateY as DateOffset

Should the first and second date within a record be already these X/Y dates you could directly apply the above example. If not you will need at first a logic to identify the X/Y within your records before you could go to a technically implementation.