maximum date

[#1]

Hi All,

My data looks like this as below.

Customer  ¦  Product number  ¦   Date

A                    222222                    10/11/2011

B                    222222                     11/11/2014

C                    233334                     10/07/2015

D                    233334                      10/08/2014

My output should be like this below.

Customer  ¦  Product number  ¦   Date

B                    222222                     11/11/2014

C                    233334                     10/07/2015

So basically if the same product number then it should display only the latest date with the customer detail. No old record should be shown.

I tried couple of ways using expression but it shows all the records and only highlights the latest one/.

Please advise here.

[sunny_talwar]

Yup you are right.

I am glad you were able to figure it out. If you got what you were looking for, I would suggest closing the thread by marking the correct answer and and helpful answers.

Best,

Sunny

[#3]

Hi All,

Their is one more issue iam facing through for the same..

here i have one more date column

Customer  ¦  Product number  ¦   Date              ¦   LastDate      

A                    222222                    10/11/2011      10/11/2012

B                    222222                     11/11/2014      10/11/2013

C                    233334                     10/07/2015       10/07/2015

D                    233334                      10/08/2014       10/07/2015

so when i check the same product number i need to check both the date columns and consider the max date//

[sunny_talwar]

What is the expected output with regards to your new sample data set here?

[#5]

Hi Sunny,

Here the output needs to be.. it has to compare all the 4 dates for ex product number 22222 - 11/11/2014 is the max date so we need to consider the output as below and if you see the data for C  and D the date columns 10/07/2015 are same across C and D . So 

B                    222222                     11/11/2014      10/11/2013

ID ¦Customer  ¦  Product number  ¦   Date              ¦   LastDate      

  1      A                    222222                    10/11/2011      10/11/2012

   2      B                    222222                     11/11/2014      10/11/2013

    3      C                    233334                     10/07/2015       10/07/2015

     4     D                    233334                      10/08/2014       10/07/2015

and if you see the data for C  and D the date columns 10/07/2015 are same across C and D . So  in such a situation it has to have a second order via ID and take the the highest ID

[sunny_talwar]

So between C and D, which row would you like to see in the output?

[#7]

in C and D .as the dates are similar.. I ahould check thru id

it should be

4     D                    233334                      10/08/2014       10/07/2015

[sunny_talwar]

See if this helps:

Dimension: Product number

Expressions:

1)

=If(FirstSortedValue(DISTINCT RangeMax(Date, LastDate), -RangeMax(Date, LastDate)) =

  FirstSortedValue(DISTINCT RangeMax(Date, LastDate), RangeMax(Date, LastDate)),

  FirstSortedValue(Customer, -ID),

  FirstSortedValue(Customer, -RangeMax(Date, LastDate)))

2)

=If(FirstSortedValue(DISTINCT RangeMax(Date, LastDate), -RangeMax(Date, LastDate)) =

  FirstSortedValue(DISTINCT RangeMax(Date, LastDate), RangeMax(Date, LastDate)),

  FirstSortedValue(ID, -ID),

  FirstSortedValue(ID, -RangeMax(Date, LastDate)))

3)

=If(FirstSortedValue(DISTINCT RangeMax(Date, LastDate), -RangeMax(Date, LastDate)) =

  FirstSortedValue(DISTINCT RangeMax(Date, LastDate), RangeMax(Date, LastDate)),

  FirstSortedValue(Date, -ID),

  FirstSortedValue(Date, -RangeMax(Date, LastDate)))

4)

=If(FirstSortedValue(DISTINCT RangeMax(Date, LastDate), -RangeMax(Date, LastDate)) =

  FirstSortedValue(DISTINCT RangeMax(Date, LastDate), RangeMax(Date, LastDate)),

  FirstSortedValue(LastDate, -ID),

  FirstSortedValue(LastDate, -RangeMax(Date, LastDate)))

[#9]

Hi Sunny,

This is yielding good results. but when I use a  search button and for example search product number 222222

I am getting good result i.e only 1 field

B 22222  B ....

But when I search using customer number for example A

my report shows still

1      A                    222222                    10/11/2011      10/11/2012

is it normal?

[#10]

Hi sunny,

is their a way to restrict when selecting the customer number as well?

[#11]

hi sunny..

was the explanation clear?