topic Re: Calculated Dimension: How to retrieve the most recent date in QlikView

Calculated Dimension: How to retrieve the most recent date

Tue, 28 Oct 2014 05:29:58 GMT

Hi,

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

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

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

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

jonathandienst — Tue, 28 Oct 2014 05:34:46 GMT

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

HTH

Jonathan

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

jagan — Tue, 28 Oct 2014 05:57:04 GMT

Hi,

Try this as Calculated Dimension

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

Regards,

Jagan.

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

Tue, 28 Oct 2014 05:57:12 GMT

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

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

jagan — Tue, 28 Oct 2014 06:06:45 GMT

Hi Matt,

Convert the format to Date like below calculated dimension

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

Regards,

Jagan.