Monthly Max Date's Sales

[#1]

It seems simple, but I am missing something or doing it wrong. I have a chart with Months  as dimension and I need to show the monthly sales on the maximum date of the relative months. I am trying something like :

Sumt( {<Date = {' $ (max(Date))'}> Sales}.   Can you tell me where I am doing it wrong?

Regards,

Amit

[#2]

Dimension : Month

Exp1 : Date(Max(Date))

Exp 2 : Sum(DISTINCT Sales)

Regards-bika

[#3]

bika_2010 wrote:

Dimension : Month

Exp1 : Date(Max(Date))

Exp 2 : Sum(DISTINCT Sales)

Regards-bika

Sum(DISTINCT Sales) - does not give the max date's sales, but the entire month's sales. The first expression is indicative (for which date sales I am talking about) i don't need that in original. To make it more clear, for Jan, if the max transaction date is 31st jan, then the sales should give the sales of 31st Jan against month Jan and similarly for rest of the months.

Please help.

[#4]

I think I am missing AGGR somewhere.  ??

[#5]

Hi Amitray

is this what you want?  (see qvw)

best regards

chris

[#6]

Thanks Chris,

PFA for my requirement

[swuehl · Accepted solution]

Try

=FirstSortedValue( aggr(sum(Sales),Date), Aggr(-Date,Date))

[#8]

You are a champion! Thanks a lot. Now I will try to understand the expression.