How to best identify items based on "last date"?

[#1]

I'm stuck....  My transactions data has customers and purchase date.  I’m trying to create a new data field that would assign each customer into a group based on the latest purchase date.  For example, customers that last bought this year should be flagged as “new” while companies that bought in prior years should be flagged as old.

Is this something best done at data load or by creating a new dimension? Either way, how would one accomplish this.  The existing variables are "purchase year" and "customer name".  I guess I'm basically asking how to find the max value of the field "Purchase Year" and create a new variable that assigns this max value to each company.

Data Format			Desired new data field "type of client"
Company	Purchase Year		Company	Type of Client
Acme	2015		Acme	new client
Acme	2012		XYZ	old client
Acme	2012
XYZ	2011
XYZ	2011
XYZ	2010

Thanks in advance for your help!!!

[jonathandienst]

On a table with Company as dimension, add the expression:

     If(Min([Purchase Year] < Max({1} TOTAL [Purchase Year]), 'old client', new client')

[sasiparupudi1]

Please see the attachement..I hope this meets your requirement

[#4]

Awesome. Thanks for jumping in to help so fast.  Much appreciated!

One problem, there seems to be a missing parentheses?

(Sorry, I'm pretty new to this and set analysis is still a bit of a mystery...)

thanks,

Pia

[#5]

Sasidhar - thank you very much for sharing an answer.  I'm amazed that I'm getting help across the globe so quickly!

Unfortunately I'm unsure/unable to open your file. I'm building QlikSense dashboards. If I click on the attachment link above, it tries to open in Qlikview but gets blocked and I get an error message about being a personal edition customer  as shown below.

Might you be able to explain what you did?  I'm trying to create a new variable e.g. during load (if.....  as "Type of Client".)

thank you very much!

[sasiparupudi1]

in the script

Data:

load * Inline

[

Company,Purchase Year

Acme,2015

Acme,2012

Acme,2012

XYZ,2011

XYZ,2011

XYZ,2010

in the front end, created a chart object

with company as a dimension and

following as an expression

if(aggr(max([Purchase Year]),Company)=Year(Today()),'New Clinet','Old Client')

hth

Sasi

[jonathandienst]

One problem, there seems to be a missing parentheses?

You are correct:

If(Min([Purchase Year] < Max({1} TOTAL [Purchase Year])), 'old client', new client')

[#8]

Thank you again.  I got this to work in creating a data table!    [But had to manually type in 2015, the year(today()) function didn't work for some reason

However, what I'd ideally want to do is create a variable upon load to store the "type of client" ie. new client/old client values so I could use them throughout the dashboard but I wasn't able to modify the above to do that.  (I tried construct "if.....(above expression)" as "Type of Client".)  The load script didn't like the "aggr" function. Any thoughts on this?

Thank you,

Pia