Set analysis equivalent in load script (rank and aggr in script)

[Anonymous]

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

[sunny_talwar]

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;

[Anonymous]

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

[sunny_talwar]

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;

[Anonymous]

Thank you so much