Match expression using max(Date)

[sibrulotte]

Hi,

I'd like to retrieve the latest text field for each specific type of values.

ie:

Rule	Date	Comment
R_AMO	2015-07-11	Fruit
R_AMO	2015-07-14	Vegetable
R_AMO	2015-07-15	Fruit
R_SSG	2015-07-11	Vegetable
R_SSG	2015-07-12	Fruit
R_SSG	2015-07-12	Vegetable
R_SSG	2015-07-13	Fruit
R_SSG	2015-07-13	Vegetable
R_SSG	2015-07-14	Fruit
R_SSG	2015-07-15	Fruit
R_SSG	2015-07-15	Vegetable
R_SSG	2015-07-16	Vegetable

Well the latest comment for R_AMO is Fruit, but the latest comment for R_SSG is Vegetable. Not that the latest date for R_AMO is 2015-07-15 but is 2017-07-16 for R_SSG.

I've got this down as an expression for my dimension "Comment" in a chart.

= if(match(Date, '$(=Max(Date))'), Comment, 'nay')

All I get are Nays

Any ideas on this?

[sunny_talwar]

Try this as your calculated dimension:

= if(match(Date, '$(=Date(Max(Date)))'), Comment, 'nay')

[sibrulotte]

Right,

R_AMO is still at nay because the Max date for the entire set is 2015-07-16... but R_AMO's maxdate is 2015-07-15...

Helpfull though thanks

[sunny_talwar]

Got it, working on it now

[sunny_talwar · Accepted solution]

Try this:

=If(Date = Aggr(NODISTINCT Date(Max(Total <Rule> Date)), Rule), Comment, 'nay')

[sibrulotte]

that's a winner right there.

[sunny_talwar]

Awesome

Glad I was able to help.

Best,

Sunny