Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Generate last 6 months for an expression

Hi,

I have an expression: if(Count(Customers)/SalesTarget>1,1,Count(Customers)). How can i filter last 6 months data using set expression?


SalesTarget - Refers to the actual target to gain customers

Customers - Refers to the current number of customers

Here, I need to use set expression to show the last 6 months data. Kindly provide a solution.

4 Replies
YoussefBelloum
Champion
Champion

Hi,

do you have a Year_month field or Month_year field on your model? otherwise what are your Date related fields ?

Anonymous
Not applicable
Author

May be like below assuming you need number of customers in last 6 months for the condition specified using a set expression -

if(Count(Customers)/SalesTarget>1,1,

Count({$<MonthField={"=if(MonthField>=MonthStart(addMonths(today(),-6)) AND MonthField<=today() ,1,0)=1"}>}Customers)

)


In case you would need your if condition as well to be based on last 6 months, you can copy the same set into if as below.


if(Count({$<MonthField={"=if(MonthField>=MonthStart(addMonths(today(),-6)) AND MonthField<=today() ,1,0)=1"}>}Customers)

/

SalesTarget>1,1,

Count({$<MonthField={"=if(MonthField>=MonthStart(addMonths(today(),-6)) AND MonthField<=today() ,1,0)=1"}>}Customers)

)


Anonymous
Not applicable
Author

Yes I do have a single date field where i convert it to a MonthYear field.

Date field is 'Status_Date'.

Anonymous
Not applicable
Author

Thanks Venkat,

Will try this expression and post the feedback.