How do you add date ranges to set analysis using existing record date?

[mjirges]

Good morning,

I am trying to put date parameters into a set analysis and am struggling with the syntax.

Part of the set analysis is checking to see if the date in the record has a corresponding record 150 to 210 days in the future.

The full formula, without the set analysis for date is:

COUNT( {$<[Client ID] = P({1<
[NOMS Record Management.1. Indicate Assessment Type: (Section)]={'Reassessment (6-month)'},
[NOMS - Adult Assessment Type.1. Enter Assessment Type: ()]={'Baseline'},
[NOMS - Adult Assessment Type.2. Was the interview conducted? ()] = {'Yes'},
[NOMS Record Management.3. Was the assessment interview conducted? (Section)] = {'Yes'}
>
}
[Client ID])>}[NOMS - Adult Assessment Type.1. Enter Assessment Type: ()])

I know this works:

,[NOMS Record Management.Test Actual Date]={"$(= '>=' & Date(Today() - 120) & '<=' & Date(Today()-110))"}

(in the example above, 120 and 110 are arbitrary numbers)

The problem is that it's going off of today's date.

This is what I need it to do:

,[NOMS Record Management.Test Actual Date]={"$('>=' & Date([NOMS - Adult Assessment Type.Test Actual Date] +150) & '<=' & Date([NOMS - Adult Assessment Type.Test Actual Date]+210))"}

I've also tried:

,[NOMS Record Management.Test Actual Date]={"=>Date([NOMS - Adult Assessment Type.Test Actual Date] + 150)<=Date([NOMS - Adult Assessment Type.Test Actual Date] + 210)"}

And;

,[NOMS Record Management.Test Actual Date] = {">=$(=Date([NOMS - Adult Assessment Type.Test Actual Date] + 150)<=$(=Date([NOMS - Adult Assessment Type.Test Actual Date] + 210)"}

I understand there might be additional work that goes into the formula, but I need help figuring out the syntax of doing the portion of the set analysis where I am taking the date of the record ([NOMS - Adult Assessment Type.Test Actual Date]) and then making sure the other date is in the timeframe of 150 to 210 days in the future.

I wanted to make sure the date was a date, so I created a dimension

=Date([NOMS - Adult Assessment Type.Test Actual Date] +150)

and it looks correct, it's calculating like it should.

Thank you!

[Kushal_Chawda · Accepted solution]

@mjirges  Not sure how and where you are going to use this expression, but issue is you are calling  Date directly within set modifier which is not the correct way to filter the Date within set. If you are going to use this condition based on latest date, probably you can try below

[NOMS Record Management.Test Actual Date]={">=$(=Date(max([NOMS - Adult Assessment Type.Test Actual Date]) + 150))<=$(=Date(max([NOMS - Adult Assessment Type.Test Actual Date]) + 210))"}

[mjirges]

What would be the best way to call the date field within the set modifier?

[Kushal_Chawda]

@mjirges  one way is I already mentioned above by applying aggregation over date. Other way is below when you want to apply filter row by row using primary key. If you don't have primary key, create it using rewno() function in script

{<Primary_key = {"=[NOMS - Adult Assessment Type.Test Actual Date]>=Date([NOMS - Adult Assessment Type.Test Actual Date] +150)  and  [NOMS - Adult Assessment Type.Test Actual Date]<=Date([NOMS - Adult Assessment Type.Test Actual Date]+210)"}>}