Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
MVP
MVP

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

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

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

sasiparupudi1
Honored Contributor III

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

Untitled.jpg

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

Not applicable

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

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

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

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
Honored Contributor III

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

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

MVP
MVP

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

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

Not applicable

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

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