Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Calculated Dimension: How to retrieve the most recent date

Hi,

I am trying to create a pivot table that includes a "Completed On" date column.

data1.PNG.png

In my data set, i have many "Completed On" dates to one "SID" (Unique identifier).

data2.PNG.png

How do i create a calculated dimension that gets the most recent date from the "Completed On" column? Currently, SID 114381 is retrieving 14/10/2014, but i want it to retrieve 17/10/2014. How is this possible?

Thanks,

Matt

1 Solution

Accepted Solutions
jagan
Not applicable

Re: Calculated Dimension: How to retrieve the most recent date

Hi,

Try this as Calculated Dimension

=Aggr(Max([Completed On]), SID)

Regards,

Jagan.

4 Replies
jontydkpi
Not applicable

Re: Calculated Dimension: How to retrieve the most recent date

Hi

=FirstSortedValue([Completed On], -[Completed On])

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jagan
Not applicable

Re: Calculated Dimension: How to retrieve the most recent date

Hi,

Try this as Calculated Dimension

=Aggr(Max([Completed On]), SID)

Regards,

Jagan.

Not applicable

Re: Calculated Dimension: How to retrieve the most recent date

Hi Jonathan,

Thanks for the quick reply.

Getting an error.

If i put your formula into the expression of a table box , it comes up with these numbers, i'm not sure what they mean, maybe you can shed some light on it.

Thanks,

Matt

jagan
Not applicable

Re: Calculated Dimension: How to retrieve the most recent date

Hi Matt,

Convert the format to Date like below calculated dimension

Date(FirstSortedValue([Completed On], -[Completed On]))


Regards,

Jagan.