Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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 FormatDesired new data field "type of client"

Company

Purchase Year

Company

Type of Client

Acme2015Acmenew client
Acme2012XYZold client
Acme2012
XYZ2011
XYZ2011
XYZ2010

Thanks in advance for your help!!!

7 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sasiparupudi1
Master III
Master III

Untitled.jpg

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

Not applicable
Author

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

Not applicable
Author

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
Master III
Master III

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
Partner - Champion III
Partner - Champion III

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')

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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