Entries with max Date to be shown

[#1]

Hi Folks,

I have a table like the one below, now I need to show only the entries with the latest Approval Date for each Client, separately.

Actual Fee is an expression.

Current table:

Required table:

Regards,

Dawar

[#2]

Hi Haimanta

In your expression for [Actual Fee] include some dynamic set analysis that filters the table by date

=Sum({<[Approval Date]={$(=Max([Approval Date]))}>}[Actual Fee])

I sometimes encounter problems when the dates are formatted in a particular way, so you may need some trial and error here.  Although it's best to have your dates as numbers and then format them in your tables and charts.

[jonathandienst]

Straight table

-dimension Client Name

-expressions

     -FIrstSortedValue([Engagement Evaluatuion ID], [Approval Date])

     -Date(Max([Approval Date]))

     -FIrstSortedValue([Actual Fee], [Approval Date])

[jonathandienst]

This will only return the global maximum date, note the maximum per Client Name.

[alexandros17]

An Example

MyTable:
LOAD * Inline [
Customer, Value, Date
A, 100, 05/06/2015
A, 120, 01/03/2015
A, 110, 11/04/2015
B, 210, 27/02/2015
B, 250, 01/06/2015
C, 105, 03/03/2015
C, 370, 17/06/2015
C, 469, 15/01/2015
D, 53, 22/01/2015
D, 78, 03/01/2015
];

Left Join
LOAD Customer, Max(Date) as Date, '1' as FlagMaxDate Resident MyTable Group By Customer;

FinalTable:
NoConcatenate
LOAD * Resident MyTable Where FlagMaxDate='1';
DROP Table MyTable;

[jduarte12]

Hello.

I would try to do the following:

1- "Client" is your dimension;

2- Use set analysis to compute your "actual fee" expression. Someting like:

actual_fee = Sum({<approval_date={"$(=max(approval_date))"}>} parameter)

Best regards,

João Duarte

[fvelascog72]

Hi,

Try this:

DATA:

LOAD

  [Client Name],

  [Engagement Evaluation ID],

  Date#([Approval Date],'DD-MMM-YY') as "Approval Date"

Inline [

Client Name, Engagement Evaluation ID, Approval Date

1FB SUPPORT SERVICIES PRIVATE LIMITED, 5046578, 11-dic-13

1FB SUPPORT SERVICIES PRIVATE LIMITED, 5063151, 7-ene-14

2WIRE DEVEPOLMENT CENTER (INDIA) PRIVATE LIMITED, 5321943, 17-jul-14

2WIRE DEVEPOLMENT CENTER (INDIA) PRIVATE LIMITED, 4488361, 16-abr-12

];

Right Join (DATA)

LOAD

  [Client Name],

  Date(Max([Approval Date]),'DD-MMM-YY') as "Approval Date"

Resident DATA

Group by [Client Name];

[jonathandienst]

João Nuno Duarte wrote:

Hello.

I would try to do the following:

1- "Client" is your dimension;

2- Use set analysis to compute your "actual fee" expression. Someting like:

actual_fee = Sum({<approval_date={"$(=max(approval_date))"}>} parameter)

Best regards,

João Duarte

See my comment above

[#9]

As mentioned by Jonathan,

I already tried doing changes in the expression before like
=Sum({<[Approval Date]={$(=Max([Approval Date]))}>}[Actual Fee])

but only the global latest Approval Date entries were only displayed

[#10]

Hi Jonathan,

This is not giving me the Engagement Evaluation ID as per latest Approval Date but instead that of the first Approval Date