Multiple table sort

[agsearle]

I have a report with many tables, but am having problems with one column of data in a chart straight table.

For this column I need to refer to data from a couple of tables like (the Meter Reading Reason is in a separate table to the other fields):

Meter Reading Date     Reading     Meter Reading Reason    

31/10/08                     789            -

15/12/08                     808            -

16/12/08                     808            06

27/04/09                     829            -

15/04/10                     873            -

16/04/10                     873            06

13/11/10                     994            -

This is a set of meter readings, and Meter Reading Reason code of '06' indicates a new customer.

I need to have a column that will show the bolded reading of 873. This being the most recent meter reading where the meter reading reason is '06'.

I've tried FIRSTSORTEDVALUE and a number of IF expressions all to no avail, any ideas?

[#2]

Why don't you sort the data in the chart by the Meter Reading Reason in a first place, and after that by the date descending?

[agsearle]

I only want to display in the straight chart just '873', I don't want to see any of the other data.

There are 4000 accounts in this report, and I only want the relevant reading to show on each one.

[#4]

In that case, when sorting, create an expresion by concatenating those two values as I have proposed previously

[#5]

Hi,

If you just want to display the '873' you can put the following expression in an text box:

=only({$<[Meter Reading Reason]={06}, [Meter Reading Date]={$(=max([Meter Reading Date]))}>} Reading)

Hope this helps

Regards!

[agsearle]

Sorry this didn't work either, even in a text box.

I do need it to be an expression in a chart straight table though.

[#7 · Accepted solution]

Hi,

Attached is an example with a solution, I changed the expression to:

=max({$<[Meter Reading Reason]={'06'}, [Meter Reading Date]={"=max([Meter Reading Date])"}>} Reading)

Maybe you will need to format your date field with the date() function in order for this to work

Regards!

[agsearle]

Thanks Gabriela that works!