Qlikview Experts..Help required for QV 11 adhoc reporting

[#1]

Hi All,

I have attached sample application img. When I pick up any dimension, qvw gets hang up.

Can anybody tell me the reason? What I can do for good performance.

My expressions are like

Total Revenue =num( Sum(Aggr( (COMMISSION/100) *
sum(TXN_COM_A + TXN_GR_PRFT_A),ACC_FA_I,FINANCIAL_ADVISOR,TRD_D,TXN_CANCN_C,TXN_FACT_I)), '#,##0')

Sell =num( Sum(Aggr(If(SubStringCount('|' & Concat(_dimension, '|') & '|', '|Split FA|') = 1,(COMMISSION/100),1) *
sum({<TXN_BUY_SELL_C={'S'}>}TXN_Q),ACC_FA_I,FINANCIAL_ADVISOR,TRD_D,TXN_CANCN_C,TXN_FACT_I)), '#,##0')

I have 1 month data. Application size is 0.7 GB. RAM is 32 GB.

When I open report tab and select and dimension, Qvw gets hang. CPU utilization reaches upto 100% though memory avaiable.

Calculation time is also high.

Pl find attached  all screen shots.

Please guide.

Thanks in advance..!!

[greg-anderson]

I would recommend creating variables or fields in your load script and populating those objects with these expressions.  That will run a lot faster than front-end calculation and should decrease CPU utilization.

[#3]

Can you provide example or let me know how to create variables in load scripts.

[greg-anderson]

Are the base fields for these calculations coming from the same table in your QV model?

[#5]

yes..

[greg-anderson]

Sorry- after looking at your expressions again, I don't know how to move the whole thing to script due to the aggregations and set analysis. The only parts to pull out of it into the script would be relatively minor.

I wouldn't know how to recommend proceeding without seeing the QVW itself.  I apologize for my error.

[greg-anderson]

You might see some performance improvement if you create this field in your table load:

SubStringCount('|' & Concat(_dimension, '|') & '|', '|Split FA| as SubStringCount

then using that in your front-end expression.  Having that text operation in the middle of the calculations could be causing some of the issues.