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
May be something like this:
Aggr_Table_Temp:
LOAD Market,
[Planning Article],
Article,
W_Period,
SIT_Lead,
RowNo() as Rank,
Sum(WS_QTY) as Sum_WS_QTY
Resident FactTable
Order By W_Period Desc
Group By Market, [Planning Article], Article, W_Period, SIT_Lead;
Aggr_Table:
LOAD Market,
[Planning Article],
Article,
Sum(If(Rank > 4 and Rank <= SIT_Lead, Sum_WS_QTY) as Sum_WS_QTY
Resident Aggr_Table_Temp
Group By Market, [Planning Article], Article;
Hi Sunny,
Thanks for the reposnse. The rowno() wouldnt work here as its a crosstable data. I tried implelementing this code but the rank nos are too high.
Regards.
Ruchi
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
Hi Sunny,
Thank you.
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))
Regards,
Ruchi
See if the attached is what you wanted
Hi Sunny,
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.
Regards,
Ruchi
Would you be able to share the script you have used for your actual data?
Hi Sunny,
Here is the script:
FactTable:
LOAD Distinct Market,
[Planning Article],
Article,
Period,
W_Period,
WS_Period,
RWFlag,
SIT_Lead,
WS_Qty,
WS_GBP
FROM
[..\Source Files\QVD\FINAL_FACTS_DATA.qvd]
(qvd) where RWFlag ='Qty';
Aggr_Table_Temp:
LOAD Distinct Market,
[Planning Article],
Article,
W_Period, !
Period,
SIT_Lead,
AutoNumber(RowNo(), Market&[Planning Article]&Article&Period) as Rank,
Sum(WS_Qty) as Sum_WS_Qty,
Sum(WS_GBP) as Sum_WS_GBP
Resident FactTable
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.
Regards,
Ruchi
Hi Sunny,
I think I got it. I used fewer fields in the key and then left joined it with the main fact table. Looks okay now. I shall update if its not working. And thank you for replying even on an old post. 🙂
Regards,
Ruchi