Expression Help

[sujeetsingh]

Hello all,

I am having a data level mismatch in a functionality.

I have

ID, Code, First Name, Last Name,Status1, Status2, Date

1 , 22     , A               ,  B              , Home  , Office , 22/5/2011

1 , 22     , A               ,  B              , Park, Shop, 22/7/2011

1 , 22     , A               ,  B              , '', Shop, 22/1/2012

1 , 22     , A               ,  B              , LANE, Office , 21/5/2012

In a straight table i am using Code, First Name, Last Name, as Dimension

Now i want to calculate ID wise latest Status1 based on date.

Straigh table will have value as

Code | First Name| Code| Status Primary

A       | B                 | 22      | LANE

Refer this thread for details

FirstSortedValue Help!!

[MK_QSL]

Your expression should be

FirstSortedValue(Status1,-Date)

Please make sure that Date is in proper format, otherwise use below in your script for Date....Otherwise FirstSortedValue will note work...

Date(Date#(Date,'DD/M/YYYY')) as Date

or

Date(Date#(Date,'DD/MM/YYYY')) as Date

[sujeetsingh]

Do not work!!

[jyothish8807]

Hi Sujeet,

Can you share your sample data?

If you don't have ID and code other than 1 and 22 then,

Firstsorted value suggested my manishkachhia should work.

Regards

KC

[sujeetsingh]

Guys i am using expression as

=aggr(FirstSortedValue([Status1], -[RowNo()]), ID)

at some places it works fine but at some location it just give the 2nd value before the latest date.

[jyothish8807]

Hi Sujeeth,

Then there is an issue with your date format.

Convert them properly.It may help.

To test that, create a text box : =max(date)

Select different ID and check if max Date is the same according to the data.

Regards

KC

[MK_QSL]

Script

Load

  ID,

  Code,

  [First Name],

  [Last Name],

  Status1, Status2, Date(Date#(Date,'DD/M/YYYY')) as Date

Inline

[

  ID, Code, First Name, Last Name,Status1, Status2, Date

  1 , 22     , A               ,  B              , Home  , Office , 22/5/2011

  1 , 22     , A               ,  B              , Park, Shop, 22/7/2011

  1 , 22     , A               ,  B              , '', Shop, 22/1/2012

  1 , 22     , A               ,  B              , LANE, Office , 21/5/2012

];

Now follow my previous post for steps

[sujeetsingh]

That is all going good

[sujeetsingh · Accepted solution]

Thanks All for you replies.

The issue i was facing in some ID is due to Sorting of the data based on Date.