topic Match expression using max(Date) in QlikView

Match expression using max(Date)

sibrulotte — Mon, 27 Jul 2015 15:05:11 GMT

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?

Re: Match expression using max(Date)

sunny_talwar — Mon, 27 Jul 2015 15:08:36 GMT

Try this as your calculated dimension:

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

Re: Match expression using max(Date)

sibrulotte — Mon, 27 Jul 2015 15:11:16 GMT

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

Re: Match expression using max(Date)

sunny_talwar — Mon, 27 Jul 2015 15:14:05 GMT

Got it, working on it now

Re: Match expression using max(Date)

sunny_talwar — Mon, 27 Jul 2015 15:18:43 GMT

Try this:

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

Re: Match expression using max(Date)

sibrulotte — Mon, 27 Jul 2015 15:21:01 GMT

that's a winner right there.

Re: Match expression using max(Date)

sunny_talwar — Mon, 27 Jul 2015 15:21:32 GMT

Awesome

Glad I was able to help.

Best,

Sunny