Aggr() , RANK() Optimization

[#1]

Dear Expert,

In a State wise Table , i am trying to calculate TRADE_VALUE of a particular Member and long with comparing TOP 5 Members of that State according to TRADE_VALUE and getting out the position of a selected Member from TOP 5 Members.

=((Sum({<RM_REGION=,CLIENTTYPE_NEW={'RETAIL'}>} TRADE_VALUE) / vmaxDay))/ sum({1} aggr( if(rank((Sum({<MEMBER_NAME=,RM_REGION=,CLIENTTYPE_NEW={'RETAIL'}>}TRADE_VALUE)/vmaxDay))<=5, (Sum({<MEMBER_NAME=,RM_REGION=,CLIENTTYPE_NEW={'RETAIL'}>}TRADE_VALUE)/vmaxDay)), STATENAME_CLNT, MEMBERID))*5

BELOW I SEPARATED THE QUERY TO UNDERSTAND IT BETTER.

1) Getting TRADE_VALUE

((Sum({<RM_REGION=,CLIENTTYPE_NEW={'RETAIL'}>} TRADE_VALUE) / vmaxDay))

2)  GETTING TOP 5 MEMBERS TRADE_VALUE OF SAME STATE DIMENSIONS

sum({1} aggr( if(rank((Sum({<MEMBER_NAME=,RM_REGION=,CLIENTTYPE_NEW={'RETAIL'}>}TRADE_VALUE)/vmaxDay))<=5, (Sum({<MEMBER_NAME=,RM_REGION=,CLIENTTYPE_NEW={'RETAIL'}>}TRADE_VALUE)/vmaxDay)), STATENAME_CLNT, MEMBERID))

3)

DIVIDING 1 AND 2 AND MULTIPLYING BY 5

SELECTIONS

MEMBER SELECTED : XYZ

MONTH-YEAR : SEKECTED

RM_REGION : <MAY SELECTED SO EXCLUDED>

THE OUTPUT IS WORKING FINE, BUT ISSUE IS ITS TAKING LOTS OF TIME IN PROCESSING CAN WE HAVE ANY BETTER WAY ( EXCEPT LOAD TIME CALCULATION )

[ganeshreddy]

Hi jitendra,

My assumption is that you are doing complex caliculations in UI level, do them in Script level then you won't find any optimezation issues.

Regards,

Ganesh

[ganeshreddy]

I mean aggrigating in script level with group by. I hope its cleared.

[#4]

Hi Reddy,

Thanks for you Reply.

Actually doing UI level provide me flexibility to easily change the logic like:

currently it is STATE level in future i can make it as CITY level also.

Current there is calculation of RETAIL business only ( as you can see in SET Analysis) but in future if we required another column of other Business Type also..

So in all that cases i needs to manage all the possibe condition at Script level only..

Regards,

Jitendra K Vishwakarma

[sasiparupudi1]

if you used the individual statements in a text object, which one is taking long time to calculate?

[ganeshreddy]

Hi Jitender,

Atleast try to do that main TRADE_VALUE caliculation in Script level, later you can have a felxibily while doing Ctiy levelcaliculations with aggr() or adding more businesses or so.

Regards,

Ganesh

[#7]

Hi,

i have to check that. but all other columns are just basic SUM() Expression even there is no distinct count also. i have also check by removing this particular expression its work fine..

i have notice that aggr() or rank() type of expression maybe run on one CPU only its not utilized the whole CPU power.

[#8]

Hi Reddy,

But once i made TRADE_VALUE on a particular Dimension then that TRADE_VALUE can work on specific Dimension only. then i cannot derived more Business information out of that.

i hope you can understand and yes if the performance is not going to improve i am going to do that only.

Thanks

[Digvijay_Singh]

Can you chk if Aggr is properly working, I am not sure how it is generating virtual table of top 5 values used to sum top 5, shouldn't it end after rank comparison? Like IF(aggr(Rank(sum(Value)),Dim)<=3. Please ignore if its irrelevant as at my level its tough to identify without actually testing it.