Webi Need Max date across multiple rows?

[former_member213525]

Hello All,

Below is my raw data in Webi and I am looking for formula which will return each employee with latest date. The tried various different ways but what it returns the max date from the whole data set.

Any help is appreciated!

Thank You.

[amitrathi239 · Accepted solution]

use this.

=max(date) in (employee)

Or create variable and filter on to get the max date row.

Var=if(date=max(date) in (employee)) then "show" else "hide"

Apply filter on Var equal to show

[former_member213525]

With Help of Amit Kumar I followed the steps and only change I did was I was using Employee Name

If(Max([V Date]) In ([Employee NAME]) = [V Date]) Then 1 Else 0

Instead I converted the date to Todate first V Date=ToDate([Date];"MM/dd/yyyy") and then

=If(Max([V Date]) In ([Employee ID]) = [V Date]) Then 1 Else 0

Worked like a charm!

[former_member213525]

Thanks Amit I am already doing this using

=If(Max([Date]) In ([Employee]) = [Date]) Then 1 Else 0

But it returns only E3 01/06/2016

[amitrathi239]

What are the value you getting if place the below formula in table one column

=max(date) in (employee)

[former_member213525]

Attached is the result I get. capture.jpg

[amitrathi239]

It seems your date object datatype is string not date..check if data type is string then first convert to date and use max.

[former_member213525]

Amit, I converted the date using below and then used it to indicate 1 or 0 but it still brings E3 01/06/2016 record.

FormatDate(ToDate([Date];"MM/dd/yyyy"); "MM/dd/yyyy")

[amitrathi239]

no it's wrong.

follow these steps.

V Date=ToDate([Date];"MM/dd/yyyy")

V Date is now with date data type.If you will use Formatdate function then it will convert to string datatype.

=If(Max([V Date]) In ([Employee]) = [V Date]) Then 1 Else 0