Pick the latest value

[apthansh]

Hi

EX: My table look like below

ID     Month    Amt

1       Jan         500

1       Feb         600

1       Mar         400

1       Apr          300

2       Jan          500

2        Feb         600

2       Mar          400

Here ID 1 should pick Apr AMT and ID 2 should pic Mar Amt.

Sum(Amt) for ID 1 should be 300 and Sum(Amt) for ID 2 should be 400.Can we do this ?

Thanks much.

[sunny_talwar]

May be like this:

Dimension

ID

Expression

FirstSortedValue(Aggr(Sum(Amt), ID, Month), -Aggr(Month, ID, Month))

[trdandamudi]

Sunny,

I tried your expression and I am getting no result... Am I missing something here ?

[trdandamudi]

May be you can give a try as below:

Data:

Load

RowNo()as RowID,*

Inline [

ID,     Month,    Amt

1,       Jan,         500

1,       Feb,         600

1,       Mar,         400

1,       Apr,          300

2,       Jan,          500

2,        Feb,         600

2,       Mar,          400

];

Dimension:

RowID

Expression:

FirstSortedValue(Aggr(Sum(Amt), RowID, Month), -Aggr(RowID,RowID,Month))

Note: If you also want to show the month name then you can also use the below expression to display the month:

Expression:

FirstSortedValue(Aggr(Month, RowID, Month), -Aggr(RowID,RowID,Month))    <--- This will display the month like Mar or April

[sunny_talwar]

What wrong trdandamudi‌, seems to be working (once Month is a dual field)

Script

Table:

LOAD ID,

  Month(Date#(Month, 'MMM')) as Month,

  Amt

Inline [

ID,    Month,    Amt

1,      Jan,        500

1,      Feb,        600

1,      Mar,        400

1,      Apr,          300

2,      Jan,          500

2,        Feb,        600

2,      Mar,          400

];

For Month, I would just do Max(Month) instead of the complicated FirstSortedValue() Expression

[trdandamudi]

Sunny,

Got it... I missed the line Month(Date#(Month, 'MMM')) as Month completely...

Thanks...

[sunny_talwar]

Well ya... if it is text, then FirstSortedValue() will never work . I assumed that Month is dual and that's why I proposed the expression.