Getting a Date from Max or Min

microwin88x — Wed, 29 Oct 2014 03:33:13 GMT

Hello,

I have a line chart for sales and a table where you can see the detail for every sale from a particular sales rep.

What I need is to get the max and min values (sales) for a specific sales rep with the corresponding date...

I've tried it using the following expressions in a text box:

MAX: =MAX(Sales)

DATE (From MAX): =DATE(SUM(AGGR(FIRSTSORTEDVALUE(Date,-Sales),[Sales Rep])),'MM/DD/YYYY')

MIN: =MIN(Sales)

DATE (From MIN): =DATE(SUM(AGGR(FIRSTSORTEDVALUE(Date,Sales),[Sales Rep])),'MM/DD/YYYY')

The expression for DATE works well when I have one single value for the MAX/MIN.

In the case of MAX, the corresponding date for 60985.12 is 01/28/2010.

But, in the case of MIN, that it's 0 for sales rep Amalia Craig, there are multiple dates, and the output shows 12/30/1899.

Do you know if there's any way to show all the possible dates for the MAX/MIN sale?

Please, find QVW attached.

Thank you!

Re: Getting a Date from Max or Min

danieloberbilli — Wed, 29 Oct 2014 03:52:44 GMT

Maybe using Rank() could help as it assigns a unique (1st) rank to one max or (last rank) to min