7 Replies Latest reply: Sep 18, 2015 4:07 AM by Jonathan Dienst

Max month data

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?

• Re: Max month data

Hi,

Can you tell still more clearly.

Thanks

Harshitha

• Re: Max month data

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

• Re: Max month data

Hi,

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

Try out with this expression.

• Re: Max month data

Hi,

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

Thanks

Harshitha

• Re: Max month data

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

• Re: Max month data

do you want his:

then use the follwoing Expression

=aggr(max(Sales),Customer)

while Customer and Month are Dimensions

• Re: Max month data

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:

Customer,

Sales,

...

FROM ...

Join(Data)

Max(Month) As Month,

1 As LastSale

Resident Data

Group By Customer;