Max month data

[#1]

Hi guys,

in my excel I want to display sales of A or B by max month

here for A sales by maxmnth is 10 , but for B sales by max month is 70.

I am using sum({<Month={'$(vMaxmnth)'>}Sales) in exp and in dimension it is Customer.

But for B it is working but for A it is not working.

Any suggestion on this? If multiple mnth entetred for customer it should pick max mnth sales but if only one mnth entered it should take that particular row.

Can you help me on this?

[#2]

Hi,

Can you tell still more clearly.

Thanks

Harshitha

[#3]

i want to show sum(sales) by  max mnth in exp and in dimension Customer.

[#4]

Hi,

=sum({<Year={$(=max(Year))},Month={$(=max({<Year={$(=max(Year))}>}Month))}>}sales)

Try out with this expression.

[#5]

Hi,

Tried with the above expression is it working fine or not.

Thanks

Harshitha

[#6]

i already hav year month calculated, so i am using that in my expression i am trying sum({<Month={'$(vMaxmnth)'>}Sales) and in dimention customer.

But it does not work for all the cases

[Anonymous]

do you want his:

then use the follwoing Expression

=aggr(max(Sales),Customer)

while Customer and Month are Dimensions

[jonathandienst]

The set expression is evaluated once for the table, not once per row, so for A, the expression reports A in August, which is zero  as there is no data for A in August.

The best solution is to add a last sales flag during reload. Something like

Data:

LOAD Month,

  Customer,

  Sales,

  ...

FROM ...

Join(Data)

LOAD Customer,

  Max(Month) As Month,

  1 As LastSale

Resident Data

Group By Customer;

Now your expression becomes:

=Sum({<LastSale={1}>} Sales)