Get Value for a Max Date

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

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!!!

Re: Get Value for a Max Date

vishsaggi — Thu, 04 Jan 2018 20:44:19 GMT

May be this? Add additional dimensions accordingly.

Dim: Store, UNIT

Expr:

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

pradosh_thakur — Thu, 04 Jan 2018 20:49:21 GMT

Try this

microwin88x — Thu, 04 Jan 2018 20:50:01 GMT

Hi Vishwarath,

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

Thanks!

pradosh_thakur — Thu, 04 Jan 2018 20:53:41 GMT

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

vishsaggi — Thu, 04 Jan 2018 20:56:17 GMT

Just use another FirstSortedValue like

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