Export to excel slow - Ad-hoc report - Conditional dimesion and expression - AJAX mode

[#1]

I have created dynamic report based on conditional show/hide of expression and dimension (Qv 11 feature). Report is generated fine but the Qlikview in-built Export to excel is taking too long (Export to excel takes 8 mins just to export 2000 rows with 30 columns).

More details

1) Report is created with Straight table having 80 dimesions and 30 expression with paramaterized variables (conditional show or hide based on user selection) So only few will be enable based on user selections.

Ad-hoc report (Straight  table)

     Dimension tab:

Expression tab:

2) I have tried export to csv using macro and its just taking 1 min but we must have to use AJAX (so i believe macro is not possible.)

3) We are using QV Version 11.2 (SR 7) and 64 bit. (I read post about some patch from QV to improve export performance but its old post (around 2012) and not sure if its applicable to this version - Extremely slow Excel export)

I believe inbuilt Export to excel trying evaluate all dimensions (all 80) and expression (all 30) even though user has selected only few and could be the root cause.

Any suggestion or design change to improve the Export to excel performance?

[amien]

Thanks for your reply.

if $(='[' & $(vDimCustomReport(1))) is evaluated .. it will result in a column name which actually exists in the data model.

SR12 worked a bit better. I also going to try to double to server resources. It worked better on my high end laptop

I hope this will work

[amien]

What are your server specs btw? and how much records do you extract on an average usage?

[#4]

Amien, I had started with the same approach $(='[' & $(vDimCustomReport(1))) was evaluating into the column name which was already exists in data model. So it will work but the performance will not be good.

1) You can try keeping the expression and add around 100 columns. You will notice perf issue.

2) Later on you can replace that express with actual data model column name in the Used Dimension and it will be much faster.

The reason behind 2) will be better because, Qlik knows what all column (dimension) you have used in your chart while rendering the chart and it will use optimized and much faster way.

For 1) it's 2 step process for each dimension and it cause perf issue.

[#5]

For my testing the sever used was 4 core CPU, 64 GB RAM and QVW size was more than 1 GB. Data around 1 million+