Look up a PRICE based on max date of Dimension grouping (set-analysis)

[evan_kurowski]

Greetings All,

I feel like I have done this exercise a million times already, but I have been infantilized and enfeebled by my current client and cannot seem to get a set-analysis expression working which will retrieve in a pivot table a column which contains the PRICE based on the maximum date associated with the dimension.

This has baffled me and now I humbly must request that someone help me with this.  Attached is a sample application which contains the raw data and a Pivot table that groups by the dimension, but I am looking for a set-analysis based expression that will return an ONLY(PRICE) based on the most recent date value stored in each IDs DATE_INTEGER field.  Any help would be much appreciated..  Thanks. 

[Anonymous · Accepted solution]

Hello Qlik Community Members- this discussion has was posted previously and answered but accidentally deleted we are recreating the thread.

Correct Answerby Jagan Mohan on Sep 21, 2012 11:05 AM

Hi,

Try this expression

=FirstSortedValue(PRICE, -DATE_INTEGER)

Hope this helps you.

Regards,

1. Jagan.

----------------------------------------

EvanKurowski Sep 21, 2012 9:35 AM (in response to EvanKurowski)

For example, I have this formula

=

Only( TOTAL <LINE_ITEM_ID> {<DATE_INTEGER={$(=Max( TOTAL <LINE_ITEM_ID> DATE_INTEGER))} > } PRICE)

And it evaluates what I'm seeking for a single LINE_ITEM_ID, but the expression does not populate for every value in the dimension.  Combinations of AGGR() with this have not produced a full listing as well.  Anyone want to weigh in?

--------------------------------

EvanKurowski Sep 29, 2012 11:28 PM (in response to Jagan Mohan)

Yes Jagan,  this will do for my purposes.

I think in the bigger pictures what I was trying to do was something akin to an ApplyMap() in a Pivot table, via set-analysis.

I was under the misimpression that I could expect the set-analysis expression to evaluate a different result for each row, but I have not figured out how to do this.

So your answer works for the needs I described, thank you.