Convert expressions of pivot table to improve performance

[Anonymous]

Hi,

I had a customized data model built in qlikview for a customer to handle any kind of DAR (Dashboard, Analytics & Reporting) requirements for the business.

Recently have come across a requirement to build a huge report which would show the performance of their business for last 3-4 years on a monthly basis with 100+ KPI's and variance reports. A subset of this particular requirement, there is a pivot table with 2 dimensions and 3 expressions. One of these expressions are of 500+ lines with number of if clauses and aggregate functions to produce KPI values based on dimension value.

It produces the accurate result expected, but i had a concern on its performance. It currently takes ~45 seconds to refresh the pivot table for just 3 months of data behind. I am sure it is not right to make user wait for a minute to see the report when data grows.

Could anyone suggest on better ways of doing this to avoid the performance issue ? Should the expression be converted into script (or) variable or any other options ?

Thanks in advance.

[sunny_talwar]

Without knowing what your dimensions and expressions are, it would be difficult to suggest any specific performance enhancements. In general, if the expressions are very complicated (using Aggr() function or complex if statements), it is better to move them to the script to gain some front end performance benefits. and same is true for calculated dimensions.