- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Can you tell still more clearly.
Thanks
Harshitha
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i want to show sum(sales) by max mnth in exp and in dimension Customer.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
=sum({<Year={$(=max(Year))},Month={$(=max({<Year={$(=max(Year))}>}Month))}>}sales)
Try out with this expression.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Tried with the above expression is it working fine or not.
Thanks
Harshitha
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
do you want his:
then use the follwoing Expression
=aggr(max(Sales),Customer)
while Customer and Month are Dimensions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)