topic Re: Selecting Low Activity Customers in QlikView

Selecting Low Activity Customers

Mon, 03 Oct 2011 15:06:05 GMT

I am looking for an expression that will limit the results of a pivot table to customers that have not ordered anything in the past 3 years.

I have tired making the dimension: If(max(Yearord)<=Year(Today())-3,name) but this is not working.

Re: Selecting Low Activity Customers

Mon, 03 Oct 2011 16:41:56 GMT

Can you create a table with name and Max(Yearord), just to check if there are any records.

Regards,

Kiran.

Re: Selecting Low Activity Customers

Mon, 03 Oct 2011 17:08:30 GMT

I am not sure what you mean.

When I create a list box that has the name as the dimension with an added expression for max(Yearord) it displays fine. There are a few customers that have no returned value.

name years ordered

a 2004

a 2006

a 2007

a 2010

a 2011

b 2004

b 2005

b 2006

c 2004

c 2005

c 2009

c 2010

With the above example, I would like to only see:

name years ordered

b 2004

b 2005

b 2006

Re: Selecting Low Activity Customers

Mon, 03 Oct 2011 17:16:16 GMT

What I meant was to list out the customers and their last order year. Just to confirm if there are any such.

So from the data only b has not placed orders in the last 3 years. You can try the following:

if(aggr(max(Yearord),name)<=year(today())-3,name).

Regards,

Kiran.

Selecting Low Activity Customers

Mon, 03 Oct 2011 17:21:16 GMT

This works. Thank you Kiran.