Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
On a table with Company as dimension, add the expression:
If(Min([Purchase Year] < Max({1} TOTAL [Purchase Year]), 'old client', new client')
Please see the attachement..I hope this meets your requirement
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
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!
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
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')
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