Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

zagzebski
Contributor

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

Tags (2)
1 Solution

Accepted Solutions
johncaqc
Valued Contributor

Re: Getting result from most recent year of activity

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

7 Replies
johncaqc
Valued Contributor

Re: Getting result from most recent year of activity

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
Contributor

Re: Getting result from most recent year of activity

Thanks.

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

Steve

johncaqc
Valued Contributor

Re: Getting result from most recent year of activity

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
Contributor

Re: Getting result from most recent year of activity

Thanks John!   Exactly what I needed.

Steve

zagzebski
Contributor

Re: Getting result from most recent year of activity

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

johncaqc
Valued Contributor

Re: Getting result from most recent year of activity

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
Contributor

Re: Getting result from most recent year of activity

Thanks....again.

Community Browser