Hi,
need help to only sum the sales of the last day of each month but not necessarily always the last day of month. It should be effected by the current choice so if the user choose day 5,6,7,8,9 - last day of a month would be the 9th.
So, I cannot work with a simple endofmonth-flag.
To simplify, lets say that I have a table with
Customer
Product
Order
Dates
Sales
Then a calender-table as well.
I tried set analysis :
sum( {$<Date = {"$(=Max(Date))"}>} Sales ) - which only give me a result for the last month. i need one result per month so I tried aggr
aggr(sum( {$<Date = {"$(=Max(Date))"}>} Sales ), Month)
- which still only gave me a result for last month. Because Max datum will always be the same no matter the aggr-function.
I tried aggr-functions without set analysis as well.
sum(aggr(if(Date = max(total <Customer, Month> Date),sum(Sales)), Customer, Month, Date))" - and this actually works as long as I only have customer as dimension.
But I would like to have a dimension group contaiting both product and customer so if I add aggr
sum(aggr(if(Date = max(total <Customer, Product, Month> Date),sum(Sales)), Customer, Product, Month, Date))"
I get a too high and aggregated value of sales.
So, any ideas?
Kind regards
Peter
