Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have used rank function and aggr in frontend which is making me app very slow. Please advice how can i do this in the backend instead.
=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))
The above expression does a sum of WS_Qty over a range of W_Period (4 to a particulat number SIT_Lead) and it is being aggregated over three fields Market, planning article and article.
Regards,
Ruchi
I have heard AutoNumber to be slow, so I am going to give you an alternative just in case you want to give it a try
Aggr_Table_Temp:
LOAD Market,
[Planning Article],
Article,
W_Period,
SIT_Lead,
AutoNumber(RowNo(), Market&[Planning Article]&Article) as Rank,
If(Previous(Market) = Market and Previous([Planning Article]) = [Planning Article] and Previous(Article) = Article, RangeSum(Peek('Rank'), 1), 1) as Rank1
Sum(WS_Qty) as Sum_WS_Qty
Resident FactTable
Group By Market, [Planning Article], Article, W_Period, SIT_Lead
Order By Market, [Planning Article], Article, W_Period Desc;
Hi Sunny,
Thanks once again. Sorry but I didnt understand this. Here, anyway we have to do autonumber to do a peek on the Rank. Sorry if I am missing something in my understanding.
Regards,
Ruchi
Well I left the AutoNumber for comparison between Rank and Rank1 field. You can remove the script which uses AutoNumber and try with the new Rank
Aggr_Table_Temp:
LOAD Market,
[Planning Article],
Article,
W_Period,
SIT_Lead,
If(Previous(Market) = Market and Previous([Planning Article]) = [Planning Article] and Previous(Article) = Article, RangeSum(Peek('Rank'), 1), 1) as Rank
Sum(WS_Qty) as Sum_WS_Qty
Resident FactTable
Group By Market, [Planning Article], Article, W_Period, SIT_Lead
Order By Market, [Planning Article], Article, W_Period Desc;
Thank you so much