Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
GeorgePhilips23
Partner - Contributor III
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)

Please help me, the above formula doesnt work.

Note: I cant make any changes in my script

Labels (3)
1 Solution

Accepted Solutions
hic
Former Employee
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
)

View solution in original post

2 Replies
hic
Former Employee
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
)

GeorgePhilips23
Partner - Contributor III
Partner - Contributor III
Author

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