Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
)
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
)
Yes, "VisitDate" did the task. Thank you Brother.