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]

Try this:

Dimension: [Product number]

Expression:

1) FirstSortedValue(Customer, -Date)

2) FirstSortedValue(Date, -Date)

[sunny_talwar]

Have a look at the attached:

[Anonymous]

Hi Rathish,

Maybe:

Table:

Load [Product number],

max(Date) as  Date

Resident ...

Group by [Product number];

left join(Table)

Load

Customer,

[Product number],

Date

Resident...

[MK_QSL]

if you have Date and Product number repeating for same Customer, use below formulas....

FirstSortedValue(DISTINCT Customer, -Date)

FirstSortedValue(DISTINCT Date, -Date)

[#6]

I tried this but I am not getting filtered. its displays the same with all values

For this FirstSortedValue(Date, -Date) I get the difference in integers like 422336.. 43336 etcc.

[#7]

Hi Both Manish and sunny is working. But iam getting this part

FirstSortedValue(Date, -Date)

FirstSortedValue(DISTINCT Date, -Date) for both as integers ..dates is not getting reflected..

Neetu 's need to check..

[#8]

Hi Rathish,

use this load script or see the attachment:

Table:

LOAD * Inline [

Customer,  Product number,   Date

A,                    222222,                    10/11/2011

B,                    222222,                     11/11/2014

C,                    233334,                     10/07/2015

D,                    233334,                      10/08/2014

];

TableOut:

Load [Product number],  

     Max(Date) as Date

Resident Table

Group by [Product number];

left join

Load Customer, [Product number], Date

Resident Table;

Drop Table Table;

[sunny_talwar]

Try this:

Date(FirstSortedValue(Date, -Date)) or

Date(FirstSortedValue(DISTINCT Date, -Date))

[#10]

Hi Guys,

It works good. Their is another way also ..from the presentation tab we could use the date option.