Generate last 6 months for an expression

[Anonymous]

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.

[YoussefBelloum]

Hi,

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

[Anonymous]

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]

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

Date field is 'Status_Date'.

[Anonymous]

Thanks Venkat,

Will try this expression and post the feedback.