Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 )
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
I mean aggrigating in script level with group by. I hope its cleared.
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
if you used the individual statements in a text object, which one is taking long time to calculate?
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
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.
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
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.