topic Re: Aggr() , RANK() Optimization in QlikView

Aggr() , RANK() Optimization

Thu, 03 Sep 2015 11:49:46 GMT

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))

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 )

Re: Aggr() , RANK() Optimization

ganeshreddy — Thu, 03 Sep 2015 12:04:36 GMT

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

Re: Aggr() , RANK() Optimization

ganeshreddy — Thu, 03 Sep 2015 12:06:44 GMT

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

Re: Aggr() , RANK() Optimization

Thu, 03 Sep 2015 12:11:54 GMT

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

Re: Aggr() , RANK() Optimization

sasiparupudi1 — Thu, 03 Sep 2015 12:20:59 GMT

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

Re: Aggr() , RANK() Optimization

ganeshreddy — Thu, 03 Sep 2015 12:24:09 GMT

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

Re: Aggr() , RANK() Optimization

Thu, 03 Sep 2015 12:24:54 GMT

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.

Re: Aggr() , RANK() Optimization

Thu, 03 Sep 2015 12:28:46 GMT

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

Re: Aggr() , RANK() Optimization

Digvijay_Singh — Thu, 03 Sep 2015 13:12:30 GMT

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.