Skip to Content
0

Webi Need Max date across multiple rows?

Apr 26, 2017 at 07:25 PM

88

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
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
10 |10000 characters needed characters left characters exceeded
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
10 |10000 characters needed characters left characters exceeded

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

=max(date) in (employee)

0

Attached is the result I get. capture.jpg

capture.jpg (16.3 kB)
0

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

0

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

0

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

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
10 |10000 characters needed characters left characters exceeded