How long were they here?

[cbushey1]

Hi there,

I am trying to figure out how long someone was here. Let me describe my data model and then the scenario.

Data Model:

I have two main dates (Start_Date and End_Date).

I have a Master calendar built off a canonical date (thanks to hic‌ blog post on Canonical dates).

Goal:

I am hoping to count how long someone was here based on what max MonthYear available.

Scenario:

Start_Date = 01/15/16

End_Date = 03/05/16

User selects MasterCalendar Month = Feb.

*I need to return 28 in this scenario as that is the number of days in Feb and this patient was here the whole month.

** Here is what I have that works well if the patient has a Start_Date or End_Date that is in one of the months the user selects when they click on [MasterCalendar Month], however if the patient has a Start_Date and End_Date outside the bounds they are not counted.

=Floor(Sum(aggr(If(END_DATE <= MonthsEnd(1,Max([MasterCalendar MonthYear])), Date(END_DATE), MonthsEnd(1,Max([MasterCalendar MonthYear]))) -
If(START_DATE > MonthsStart(1,Min([MasterCalendar MonthYear])), Date(START_DATE), MonthsStart(1,Min([MasterCalendar MonthYear]))),ACCOUNT_NO) +1))

Thanks

[marcus_sommer]

One possibility might be to match your start and end dates with the master-calendar per IntervalMatch. Another could be the use of set analysis, maybe something like this:

count({< Date = {"=Date>=Start_Date<=End_date"}>} Date)

- Marcus

[cbushey1]

Hi Marcus,

Thank you for the reply but I am not sure IntervalMatch will work in this case since my Master Calendar was actually built off both of those fields by way of canonical date.

I think the count approach could work but I am not sure what dates you reference with "Date". In my data model I have many dates. I also have 3 calendars, 1 based off each date (START_DATE, END_DATE) and 1 is the master calendar using a canonical date. The other thing is that I expect the users to make selections based on [MasterCalendar Year] and [MasterCalendar Month] not at the date level.

Do you have any ideas for converting your suggestion to be MonthYear driven instead of date?

Chase

[marcus_sommer]

The use of three different calendars and a canonical calendar to merge them must no be a obstacle to associate a link-table created per intervalmatch (or alternativel per while-loop) respectively to join these data to one of your other tables (would be probably more performant by larger datasets).

It's difficult to give a concrete advice by your datamodel then it will be depend how these should be displayed. With a set analysis approach like suggested above you will be able to ignore selections but as far as you need to use $-sign expansion in it like $(=min(Date)) you couldn't calculate them on a row-level within the chart.

This meant you might need a combination of set analysis whereby the min/max/monthstart ... could be outsourced in variables with if-loops and maybe wrapped by aggr-functions.

- Marcus

[cbushey1]

I have tried to incorporate the set analysis approach but I know I am still not quite there yet. Here is what I have:

=Floor(Sum(aggr(Sum({<CanonicalDate = {">=$(=min(START_DATE))<=$(=max(END_DATE))">}$(=If(END_DATE <= MonthsEnd(1,Max([MasterCalendar MonthYear]))
, Date(END_DATE), MonthsEnd(1,Max([MasterCalendar MonthYear])))) -$(=
If(START_DATE > MonthsStart(1,Min([MasterCalendar MonthYear]))
, Date(START_DATE), MonthsStart(1,Min([MasterCalendar MonthYear]))))),ACCOUNT_NO) +1))

I know this is might be a bit hard to follow and is extremely complex, but basically what I am trying to do is first determine if I should use the END_DATE or the Max available date using the max and monthend functions. I do the same for START_DATE (only using monthstart instead of end) and then subtract the 2 to get the number of days in between. Since there are multiple accounts (rows) in the system I need to aggregate this number of days for each ACCCOUNT_NO. I then sum this together so that I get the total number of days and I use the floor function to give me only the whole number.

This doesn't yield the exact results I would be expecting but I know I must be close.

[marcus_sommer]

It's from your expression quite difficult to say what didn't worked like expected. My approach by such long and complicated expressions is to split them in each single part to see if they work and if they work to put them together step by step.

- Marcus

[cbushey1]

Marcus,

I understand that it is hard to say what did or did not work, and as I mentioned it is a complex code. I have broken my problem down and came up with 3 scenarios. Two of those scenarios work with the code that is in gray. The third scenario is the one I have been trying to get help with. I guess I will wait and see if anyone else has any suggestions...