How to retrieve max record for a given month or year?

[#1]

Hi All,

I have an example table as the following:

Customer table:

Transaction Date, Channel Code, Beginning Balance, Order Value, Ending Value

4/25/2014               CH1                    100                         30               130

4/28/2014               CH1                    130                         10               140

4/30/2014               CH1                    140                         20               160

5/15/2014               CH1                    140                         20               160

5/31/2014               CH1                    160                         20               180

I have other dimensions year, month, day, and channel. So, my question is how can I retrieve the max record for a given month or year? I mean in case I choose month of April 2014 I want the table to show only the record associated with the 4/30/2014?

Thanks!

[rwunderlich]

In most cases, you can use the FirstSortedValue function.

=FirstSortedValue([Ending Value], -[Transaction Date])

-Rob

[#3]

Thanks Rob, however, applying that raised an error "Error in calculated dimension". On the other hand, Isn't that function retrieve the first value? cause what i'm looking at is the last one instead.

Thanks again, Louie

[JonnyPoole · Accepted solution]

I changed the chart expression to reference the Maximum Date in the selection as follows

sum( {$<Date={'$(vMaxDate)'}>} Sales)

....where vMaxDate is a variable with this definition:   =Date(max(Date),'MM/DD/YYYY')  

This focuses the chart to just show records for the max date of the selection.

in this example, Date is my date field from the data source which in a list box renders as MM/DD/YYYY values

[rwunderlich]

I was expecting that you would use this as an Expression, not a Dimension.

The "-" in the second parm selects the last vs the first value.

[#6]

Well, I tried it as an Expression but it showed everything (the filter didn't work) therefore I tried the Dimension thing!

[#7]

It has worked now... I had other columns that were listed as dimensions as well. I removed them and voila! Thank you Rob!