Help with a formula to filter by no meetings in the last 180 days.

[HeathBiller]

Hi, I am trying to add a slice for some data and I am looking for help with a formula. I have [Households] as one data source and [Meetings] as another data source. The [Households] is the parent data and the [Meetings] is ref to the [Households] as the child data.

I would like to filter the slice so it only shows Households who have not had any meetings documented in the last 180 days. Each meeting has a date field that is mandatory to be filled out. I've been trying to think of a formula for this but have not been able to come up with one. Any ideas are welcome. Thanks in advance.

[peterson]

Hello my dear.

In this situation I can imagine one scenario. 

1. Create a virtual column in that parent table to define a status. It has to be something like that: IF(TOTALHOURS(MAX([Ref Meeting][Date]))/24 >180, "Inactive","Active")
2. I didn't really wrote a true formula it is just to represent the logic behind it. You need a conditional that says if the last meeting in the related child table is more than 180 days than it is inactive, otherwise, active. 
3. To achieve this result I used TotalHours() divided by 24 which gives me the total of days. This way, I have a dynamic status that will auto change when the meeting is too old.
4. That done, now you create a slice using the Status as a conditional to filter data. Now you can set what old and recent registrations.

I hope I could help

[scott192]

I would approach this the following way.

Set up a Slice on Meetings that will have all meetings that have occured in the last 180 days.  The formula to use would be [Meeting Date] + 180 >= TODAY()

Add a VC to your Households table that flags if a meeting has not occurred based on your slice.  The formula to use would be NOT(IN([_THISROW].[Household Key Field], MeetingSlice[Related Household Key Field])).  This will be set to TRUE if a meeting has not occurred in the last 180 days and FALSE otherwise.

Now You could make a Household slice where the filter value for your VC is set to TRUE and that will leave you with a slice that has Households that haven't had a meeting in the last 180 days.

[HeathBiller]

I like that approach and think it would work. For the VC though I am having trouble with the formula. I am unsure if you are able to have a "Slice" of data included in the formula. If so any ideas how as I am unable to figure out how to link the sliced data in that VC formula.

[scott192 · Accepted solution]

What's the name of your slice ? 😎

[HeathBiller]

I have the slice named "180 Day"

[scott192]

So the VC would be something like
 NOT(IN([_THISROW].[Household Key Field], 180 Day[Ref Household Key Field])).

Be careful though!  Tip from the Top... Don't use spaces to name your tables, slices and fields.  I would much prefer your slice was called 180Day instead of 180 Day.  If you MUST add some sort of separator it would be best to use an underscore.  180_Day.  Field_Name. etc.  If you ever attempt to lift your data to a SQL database you will have to rename all of your spaced out names and fields and underscore (_) is pretty generally used in big DBMS.

[HeathBiller]

Great thanks! I thought I tried that before so maybe the space was messing it up. I changed the slice name to 180_Day and now the formula seems to work. Thanks for the advice on not using spaces or putting in an underscore instead. I appreciate the help.

[scott192]

That's really great to hear!  I did wonder about the space and did a brief search to see if having it would mess things up.  Couldn't find anything too quickly though so I assumed you would try without a space 😁