Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

sibrulotte
Contributor III

Match expression using max(Date)

Hi,

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

ie:

      

RuleDateComment
R_AMO2015-07-11Fruit
R_AMO2015-07-14Vegetable
R_AMO2015-07-15Fruit
R_SSG2015-07-11Vegetable
R_SSG2015-07-12Fruit
R_SSG2015-07-12Vegetable
R_SSG2015-07-13Fruit
R_SSG2015-07-13Vegetable
R_SSG2015-07-14Fruit
R_SSG2015-07-15Fruit
R_SSG2015-07-15Vegetable
R_SSG2015-07-16Vegetable

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?

1 Solution

Accepted Solutions

Re: Match expression using max(Date)

Try this:

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

Capture.PNG

6 Replies

Re: Match expression using max(Date)

Try this as your calculated dimension:

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

Capture.PNG

sibrulotte
Contributor III

Re: Match expression using max(Date)

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)

Got it, working on it now

Re: Match expression using max(Date)

Try this:

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

Capture.PNG

sibrulotte
Contributor III

Re: Match expression using max(Date)

that's a winner right there.

Re: Match expression using max(Date)

Awesome

Glad I was able to help.

Best,

Sunny

Community Browser