topic Qlikview Experts..Help required for QV 11 adhoc reporting in QlikView

Qlikview Experts..Help required for QV 11 adhoc reporting

Tue, 26 Nov 2013 17:00:30 GMT

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..!!

Re: Qlikview Experts..Help required for QV 11 adhoc reporting

greg-anderson — Tue, 26 Nov 2013 17:51:02 GMT

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.

Re: Qlikview Experts..Help required for QV 11 adhoc reporting

Tue, 26 Nov 2013 17:59:13 GMT

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

Re: Qlikview Experts..Help required for QV 11 adhoc reporting

greg-anderson — Tue, 26 Nov 2013 18:05:06 GMT

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

Re: Qlikview Experts..Help required for QV 11 adhoc reporting

Tue, 26 Nov 2013 18:06:17 GMT

yes..

Re: Qlikview Experts..Help required for QV 11 adhoc reporting

greg-anderson — Tue, 26 Nov 2013 18:18:35 GMT

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.

Re: Qlikview Experts..Help required for QV 11 adhoc reporting

greg-anderson — Tue, 26 Nov 2013 18:24:15 GMT

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

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.