Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Monthly Max Date's Sales

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

 

16 Replies
Not applicable
Author

Dimension : Month

Exp1 : Date(Max(Date))

Exp 2 : Sum(DISTINCT Sales)

Regards-bika

Not applicable
Author

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.

Not applicable
Author

I think I am missing AGGR somewhere.  ??

Not applicable
Author

Hi Amitray

is this what you want?  (see qvw)

best regards

chris

Not applicable
Author

Thanks Chris,

PFA for my requirement

swuehl
MVP
MVP

Try

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

Not applicable
Author

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