Calculations within a daterange - help!

[#1]

Hi Community and thank you for all helpful your posts!

I am trying to figure out the best way of performing a certain calculation and I would really appreciate your input on this.

I will try to explain what I'm after using some sample data. Here goes.

I have patient data, with patient ID (patID) and AppointmentDate (YYYY-MM-DD), coupled to a specific Clinic:

I also have invoice data, with InvoiceNr, InvoiceDate (YYYY-MM-DD) also coupled to a Clinic:

Here's what I'd like to do:

I want to count eg. the number of PatIDs that have visited the clinic during a specific period of time. That period is defined as the period between invoices, plz consult the color-coded picture below:

So, for Invoice Nr 3003, I want to see all patients between 2016-02-08 and before 2016-03-01. In this example only patient with ID 105.

Resulting in:

How would you accomplish this?

Kind Regards,

Nathalie

[#2 · Accepted solution]

Hi guys!

With some exemplary help from liivirimling, I got this to work!

So I thought I'd share the solution.

I need to use IntervalMatch, so I need to have a start and a stop date to create the interval. The InvoiceStop is the day before the Invoice Date (My sourcedata gives invoice date as a TimeStamp, thus the use of Floor() and Date() function):

[InvoiceData]:

LOAD DISTINCT

    Date(Floor(invoicedate), 'YYYY-MM-DD') as InvoiceDate,

    Date(Floor(invoicedate)-1, 'YYYY-MM-DD') as InvoiceStop,

    "invoice number" as InvoiceNr,

    clinic as Clinic

That took care of the Stop, now for the Start:

Left Join (InvoiceData)

LOAD DISTINCT

    Clinic,

    InvoiceNr,

    if(previous(Clinic)=Clinic, Date(Previous(InvoiceStop+1)), yearstart(InvoiceDate)) as InvoiceStart

Resident InvoiceDate

order by Clinic, InvoiceDate;

So, I sort my data by Clinic and then by InvoiceDate and then use the Previous() function to fetch the date of the previous invoice as InvoiceStart, but only for as long as the Clinic is the same.

Now we have a Start and a Stop. We proceed to IntervalMatch.

IntervalMatch(AppointmentDate, Clinic)  //States which field to match, states Key Field

Left join (PatientData)  //States which table to join the results

LOAD

      InvoiceStart,

      InvoiceStop,

      InvoiceNr

Resident [InvoiceData];

IntervalMatch now checks patient appointment date with the starts and stops.

Lastly. I joined the InvoiceNr to the PatientData table and drop the unnecessary fields:

Left join (PatientData)

LOAD

  InvoiceStart,

    InvoiceStop,

    Clinic,

    InvoiceNr

Resident [InvoiceData];

Drop fields InvoiceStop, InvoiceStart, Clinic

FROM PatientData;

[tamilarasu]

Great Nathalie. Kindly close this thread by marking your own response as correct answer. Have a nice day.

[#4]

Hi Tamil,

Just did. Just hade to put some finishing touches on the reply. Hope it helps someone else struggling with this.

Have a nice day! 😃