Can you share some sample data with expected output or your application to see what you are doing? I gave you a general script because I don't know the specific, may be if you can share the specifics, I can help you better
PFA a sample. Required output is to find sum of qty of each country over a period. This period is maximum fourth position of Period to SIT_Lead position in Period. this has to be aggregated over Market,[Planning Article],Article.
=sum(aggr(sum(Aggr( if(Rank(W_Period)>4 and rank(W_Period) <= SIT_Lead ,sum( WS_Qty)),Market,[Planning Article],Article,W_Period)),Market,[Planning Article],Article))
Sorry for not replying on this post. I worked on it now. Although the soluntion works when i use sample data, but when I use actual data, the Autonumber just goes on and on to reload. Is there any other alternavtive? Thank you.
AutoNumber(RowNo(), Market&[Planning Article]&Article&Period) as Rank,
Sum(WS_Qty) as Sum_WS_Qty,
Sum(WS_GBP) as Sum_WS_GBP
Group By Market, [Planning Article], Article, W_Period, SIT_Lead,Period
Order By W_Period Desc;
Please note that this autonumber with so many fields is causing the problem. I tried taking in separate loads, making the fields combo as key outside and using that directly, calculating rowno outside etc. but no use.
And I need to have these many fileds here. Please advice.