Set analysis to filter based on day

[mpnewbie]

Hi,

I need to create a flag if a subject has received Med1 within 2 months prior to the latest Test2.

Given the following:

MedTable:

Subject | MedName | Date | DayNumber

Sub1 | Med1 | 2017-09-02 | 3

Sub1 | Med2 | 2018-09-02 | 368

Sub2 | Med1 | 2018-09-02 | 7

Sub3 | Med1 | 2018-09-10 | 53

TestTable:

Subject | TestName | Date | DayNumber

Sub1 | Test1 | 2017-09-05 | 6

Sub1 | Test2 | 2018-09-05 | 371

Sub2 | Test2 | 2018-09-05 | 10

Sub3 | Test2 | 2018-09-05 | 48

The desired output would only flag Sub2.

However, the flag is only working some of the time for me (there are others like Sub2 that should get flagged but are not), and I am unsure of how to include the requirement that Med1 must be prior to Test2 (Sub3 should not be flagged since Med1 was taken after Test2).

My current expression is:

if(
  Count(
    {$<
      [MedTable.MedName]={'Med1'},
      [MedTable.DayNumber]={">=$(=max(if([TestTable.TestName]='Test2', [TestTable.DayNumber]))-60)"}
    >}
    [MedTable.MedName]
  )>0,
  'FLAG'
)

Thanks!

[JordyWegman]

Hi Mp,

Would it be an idea to join these tables? I think it would make your model a bit easier. It is correct that Sub1 | Med1 is connected to Sub1 | Test 1?

Jordy

Climber

[mpnewbie]

Unfortunately, I can't join the tables together because the app I'm using only gives me access to the visualization piece. I'm unable to make changes to the data model.

The tables are only linked by Subject. While tests happen on a schedule, the medications can be taken at any time.

[JordyWegman]

Ah that makes it a bit hard, because you have a many to many relationship then. This creates a synthetic key in your data model. I'll try to come up with a solution for this.

Why is Sub1 not flaged because it also has the right conditions, right?

Jordy

Climber

[mpnewbie]

Sub1 is not flagged because Med1 was taken more than 2 months prior to their Test2 date. 

Your help is much appreciated!

[JordyWegman]

The hard thing is trying to understand the relations between the data. Because Sub1 from Med1 is related to Sub1 with Test2. But is it also related to Test1? How do you make this relationship? 

It's an interesting problem!

[mpnewbie · Accepted solution]

It's a little ugly and there's probably a better way, but I got it working:

if(

  (

    Aggr(max({$<[TestTable.TestName]={'Test2'}>} [TestTable.DayNumber]-90), [TestTable.Subject])<=

    Aggr(max({$<[MedTable.MedName]={'Med1'}>} [MedTable.DayNumber]), [MedTable.Subject])

  ) and (

    Aggr(max({$<[TestTable.TestName]={'Test2'}>} [TestTable.DayNumber]), [TestTable.Subject])>=

    Aggr(max({$<[MedTable.MedName]={'Med1'}>} [MedTable.DayNumber]), [MedTable.Subject])

  ),

  'FLAG'

)