topic Re: Generate last 6 months for an expression in QlikView

Generate last 6 months for an expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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 — Fri, 22 Jun 2018 15:09:27 GMT

Hi,

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

Anonymous — Fri, 22 Jun 2018 15:21:04 GMT

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 — Mon, 25 Jun 2018 05:52:07 GMT

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

Date field is 'Status_Date'.

Anonymous — Mon, 25 Jun 2018 05:52:57 GMT

Thanks Venkat,

Will try this expression and post the feedback.