Set expression for last six months

[GeorgePhilips23]

I have created a field in my script as:

month(VisitDate)&'-'&Year(VisitDate) as Visit_Month

I have to make a set expression for 6 months using visit month(in MMM-YYYY format)

count({< State={"Positive"}, Visit_Month={>=$(=MonthStart(Max(Visit_Month), -6))<=$(=MonthEnd(Max(Visit_Month)))} >} distinct VisitNumber)
/
count({<Visit_Month={>=$(=MonthStart(Max(Visit_Month), -6))<=$(=MonthEnd(Max(Visit_Month)))}>}distinct VisitNumber)

Please help me, the above formula doesnt work.

Note: I cant make any changes in my script

[hic · Accepted solution]

You have defined Visit_Month as a non-numeric string, and then it is very difficult to make an expression based on a numeric value. The following would have been better:

Date(MonthStart(VisitDate),'MMM-YYYY') as Visit_Month

So you will have to base your expression on VisitDate instead. And you need double quotes for the search.

Try

count(
{<
State={"Positive"},
VisitDate={">=$(=MonthStart(Max(VisitDate),-6))<=$(=MonthEnd(Max(Visit_Date)))"}
>}
distinct
VisitNumber
)
/
count(
{<
VisitDate={">=$(=MonthStart(Max(VisitDate),-6))<=$(=MonthEnd(Max(VisitDate)))"}
>}
distinct
VisitNumber
)

[GeorgePhilips23]

Yes, "VisitDate" did the task. Thank you Brother.