Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am looking for help on an expression that would give me the number of members for a customer for the most recent year of activity. (which could be different for each customer)
RAW DATA:
Customer Year Members
A 2013 25
A 2014 50
B 2012 75
B 2013 100
C 2010 100
C 2012 150
RESULTS:
Customer Members from most recent year of activity
A 50
B 100
C 150
Thanks in advance for any help,
Steve
Gotcha.
This should return both Year and Members in expressions with Customer as Dimension;
For Members:
FirstSortedValue (Total <Customer> Members, -Year)
For Year:
FirstSortedValue (Total <Customer> Year, -Year)
HTH,
John
Hi Steve,
Based on the data presented;
RAW_DATA:
Load * Inline [
Customer,Year,Members
A, 2013, 25
A, 2014, 50
B, 2012, 75
B, 2013, 100
C, 2010, 100
C, 2012, 150
];
DATA:
NoConcatenate
Load
Customer,
LastValue(Year) as Last_Year,
LastValue(Members) as Last_Members
Resident RAW_DATA
Group By Customer;
(and optionally)
Drop Table RAW_DATA;
HTH,
John
Thanks.
Sorry John - my fault I wasn't specfic. Looking to do this in an expression on the front end if possible?
Steve
Gotcha.
This should return both Year and Members in expressions with Customer as Dimension;
For Members:
FirstSortedValue (Total <Customer> Members, -Year)
For Year:
FirstSortedValue (Total <Customer> Year, -Year)
HTH,
John
Thanks John! Exactly what I needed.
Steve
Okay (and if this takes alot of thought don't worry about it) - is there a way to say it has to be a certain amount for it to grab that month. For instance in the data above it sort like you did but the value has to be at least 75, for example? -
Steve
Try creating a variable such as vMinValue, then change the expression to this;
For Members:
FirstSortedValue ( {<Members={'>=$(vMinValue)'}>} Total <Customer> Members, -Year)
For Year:
FirstSortedValue ( {<Members={'>=$(vMinValue)'}>} Total <Customer> Year, -Year)
Adjust the variable to see it in action.
Thanks....again.