Solved: Get Value for a Max Date - Qlik Community

microwin88x · ‎2018-01-04

Hello,

I have the following table:

Note: The Date has format DD/MM/YYYY

What I need is to get the Contract for the max Date by Store and Unit.

Please, note that a Store could have more than 1 Unit for the max Date (for example, with ST 1).

I should get something like this:

Do you know how could I do that on a chart expression?

My dimensions should be Store and Unit, and my expression the last Contract.

Thank you!!!

pradosh_thakur · ‎2018-01-04

sum({<DATE={"=(DATE=Max(TOTAL <STORE,UNIT>DATE))"}>}CONTR)

Learning never stops.

vishsaggi · ‎2018-01-04

May be this? Add additional dimensions accordingly.

Dim: Store, UNIT

Expr:

= FirstSortedValue(CONTR, -Aggr(CONTR, STORE, UNIT, CONTR))

pradosh_thakur · ‎2018-01-04

Try this

Learning never stops.

microwin88x · ‎2018-01-04

Hi Vishwarath,

Do I have to put the Date somewhere? How does it knows which is the last contract?

Thanks!

pradosh_thakur · ‎2018-01-04

sum({<DATE={"=(DATE=Max(TOTAL <STORE,UNIT>DATE))"}>}CONTR)

Learning never stops.

vishsaggi · ‎2018-01-04

Just use another FirstSortedValue like

= Date(FirstSortedValue(DATE, -Aggr(CONTR, STORE, UNIT, CONTR)))

Get Value for a Max Date