Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Partner - Contributor III

## Set expression for last six months

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)

Note: I cant make any changes in my script

Labels (3)

• ### Visualization

1 Solution

Accepted Solutions
Former Employee

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
)

2 Replies
Former Employee

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
)

Partner - Contributor III
Author

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

Tags
Community Browser