0

# Webi Need Max date across multiple rows?

Apr 26, 2017 at 07:25 PM

88

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.

capture.jpg (30.7 kB)

AMIT KUMAR
Apr 26, 2017 at 07:54 PM
0

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

Share
Sam Govaski Apr 26, 2017 at 08:16 PM
0

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

Show 5 Share

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

=max(date) in (employee)

Attached is the result I get. capture.jpg

capture.jpg (16.3 kB)

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

AMIT KUMAR

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")

no it's wrong.

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

Sam Govaski Apr 27, 2017 at 03:45 PM
0

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!

Share