Issue with showing a record with the latest date in a chart

[#1]

I am trying to get the location of a Serial numbered stock item (SERIALNUMBER) in a table chart by looking for the record which has a status of "I" (STATUS)and looking for the latest date

For example I have 2 records - the 1^st with Location A on 1/8/2009 and the 2^nd record with Location B on 30/4/2010.

The result I would like to see is Location B with a date of 30/4/2010

If I use AGGR & MAX functions as shown below, I get the correct date in the expression but the 1^st record in the Dimension showing Location A

aggr(max({$<STATUS = {'I'}>} DATE), SERIALNUMBER)

If I use the FirstSortedValue function with a - Date, I get both records showing with the serial number in the expression

FirstSortedValue ({$<STATUS = {'I'}>} SERIALNUMBER, - DATE)

Any suggestions anyone?

[maneshkhottcpl]

Hi,

Please refer the attached application.

[#3]

Hi Manesh - Thanks for your reply. I'm not sure this works for me though as I have changed the input to be more like I would get (I assume OutPut would be in my case Location).

Date1, Status, OutPut,SERIALNO

1/1/2009, I, P, 1

1/1/2010, O, P, 1

1/1/2011, I, Q, 1

1/1/2009, I, Q, 2

1/1/2010, O, Q, 2

1/1/2011, I ,R, 2

The results should be Date 1/1/2011 output Q for SERIALNO 1 & 1/1/2011, R, for SERIALNO 2

[#4]

Try the expression below:

aggr(max({$<[STATUS]={'I'}>} total <SERIALNUMBER> DATE), SERIALNUMBER, LOCATION)

I've used something similar to the above expression many times in similar situations. The key is that you need to have location in the aggr but you have to use total <SERIALNUMBER> because you want the highest date for each SERIALNUMBER, not for each location.

[#5]

Hi Trent - thanks for replying.

I have done what you suggested and the expression itself works fine giving me the lastest date but I'm still showing the 2 records with the different LOCATIONs. I only want to see the latest location of that SERIALNUMBER.

I would have thought the AGGR function would have taken care of that but so far - no success!

[#6]

Have you tried putting the formula into your dimension?

If(Date=aggr(max({$<STATUS = {'I'}>} DATE), SERIALNUMBER),Location)

[#7]

Hi Aline

Thank you for your help

Your suggestion shows the correct location but nulls the location in the second record. However the 2nd record is still being displayed in the table.

I need to be able to aggregate the two records but only show the correct (last) location.

[#8]

Try adding this exact formula as an expression. On the presentation tab, check the option of Hide Column for the dimension so that you won't see it twice.

[#9]

Hi Aline

I think this will work but I have now discovered there is some corruption in the source data which is causing other complications preventing this from working so I need to get this corrected. It may take a few weeks as I am depending on an outside company to do the work and I don't have a time frame on this. However I will let you know how this works out later.

Thanks everyone who contributed!