Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Slow Report

Hello,

I have a report that is running quite slow. the report shows all employees per month and the monthly payment they received per category. The user must choose a category (they can also choose more than one but the problems happens even if they pick just one)

Also, the user can select to show only payments that are below a parameter they inputted (using a variable and an input box).

I am doing the filtering using Set Analysis, in the following syntax:

Sum ({$<ActiveMonthFlag={0},EmpID={"=Sum ({$<ActiveMonthFlag={0}>} PayAmount)<$(vMinAmount)"}>} PayAmount)

This is what i have noticed:

1. When I select a category that only some of the employees have (regardless if the PayAmount is below the parameter, no of rows 251), the report works fine and quite fast.

2. When I choose a category that all employees (no of rows: 14240) have and then the system has to filter from all of them, only the ones that have a PayAmount below the parameter, the report works very very slow.

3. If I remove the set analysis EmpID={"=Sum ({$<ActiveMonthFlag={0}>} PayAmount)<$(vMinAmount)"} the report is much faster

Also

4. There is a big difference between the time it takes for the report to run on the Qlikview Client (run locally on the server) and via Ajax, which is much slower.

In the past we have tried using IF(aggr... in the dimension but i believe it was even slower.

Any thoughts?

Thank you!

Nir

1 Solution

Accepted Solutions
Not applicable
Author

Hello,

I have found the problem, the expression is perfect and the problem was that I has this expression in the Background color of one of the dimensions: "if(Remark='',red())" Which was meant to color all employees that a remark was written for them in the system in Red. Remark is an input field. This caused severe performance issues and as very glad we found the problem, now all my reports work really really fast even with over 50M records in the fact! My very important conclusion is to minimize, or if all possible, eliminate all complex IF statements from Dimensions and use only Set analysis in the Expression (Even if the Set analysis seems complex to write or understand) it will simply work much much faster!

View solution in original post

6 Replies
cesaraccardi
Specialist
Specialist

Hi Nir,

Have you tried replacing the IF(aggr(... by IF(sum(total<Dimension>... ?

Kind Regards,

Cesar

Not applicable
Author

I cannot use an IF condition in the Dimension without aggr() function. what did u refer to?

thanks

cesaraccardi
Specialist
Specialist

Sorry Nir, I mean in the expression...

Can you do something like:

if(sum(total<Category> PayAmount)<$(vMinAmount),sum(PayAmount))

You might have to hide the null values if that works.

Cheers,


Cesar

Not applicable
Author

Hi Cesar,

I have tried this option and it seems to work a little bit better. is there no way to implement a condition with a parameter and have it run fast. I always seems that when a parameter is involved the query take a long time

thanks

nir

teempi
Partner - Creator II
Partner - Creator II

Hi,

You could try aggregating the payment totals in the script assuming you only need the monthly figures. This might help if the montly payment consists of many rows.

Create a new key for your monthly payments, something like autonumberhash256(emplid, yearmonth) as payment_key, then create a new table and load the payment data again via resident load and aggregate (sum) the data by payment_key. Then in your expression you might be able to use something like {< ... monthly_payment_sum={"<$(vMinAmount)"} .. >}

-Teemu

Not applicable
Author

Hello,

I have found the problem, the expression is perfect and the problem was that I has this expression in the Background color of one of the dimensions: "if(Remark='',red())" Which was meant to color all employees that a remark was written for them in the system in Red. Remark is an input field. This caused severe performance issues and as very glad we found the problem, now all my reports work really really fast even with over 50M records in the fact! My very important conclusion is to minimize, or if all possible, eliminate all complex IF statements from Dimensions and use only Set analysis in the Expression (Even if the Set analysis seems complex to write or understand) it will simply work much much faster!