Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zagzebski
Creator
Creator

Getting result from most recent year of activity

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

1 Solution

Accepted Solutions
johnca
Specialist
Specialist

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

View solution in original post

7 Replies
johnca
Specialist
Specialist

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

zagzebski
Creator
Creator
Author

Thanks.

Sorry John - my fault I wasn't specfic. Looking to do this in an expression on the front end if possible?

Steve

johnca
Specialist
Specialist

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

zagzebski
Creator
Creator
Author

Thanks John!   Exactly what I needed.

Steve

zagzebski
Creator
Creator
Author

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

johnca
Specialist
Specialist

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.

zagzebski
Creator
Creator
Author

Thanks....again.