topic Re: Reduce calculation time in QlikView

Reduce calculation time

kylealpaugh — Wed, 25 Nov 2020 16:16:04 GMT

I have a very long expression in one of my charts and when it runs it comes up with a calculation time out error. Its a conditional statement is there any way to make this expression shorter or make it so it calculates quicker ?

if( Correct_flag_aggr='X',Correct_amount_aggr,
       if(sum({< [code_Customer Segment]-={'IC'}>}[Bill_Amount_aggr Entity]*LastYTDFlag_aggr)>0
             and rangesum((Sum ({<[code_Customer Segment]-={'IC'}, Source={'Aggr_bill'}>}( Bill_Qty_aggr * CurYTDFlag_aggr ))
             -
                    Sum ({< [code_Customer Segment]-={'IC'}, Source={'Aggr_bill'}>}( [Bill_Qty_aggr] * LastYTDFlag_aggr )))
                    /
                           Sum ({< [code_Customer Segment]-={'IC'}, Source={'Aggr_bill'}>}( [Bill_Qty_aggr] * LastYTDFlag_aggr )))>.5
                                 and [Created on_ERSDA] - $(vEffDate)>-720,0,
             if(sum({< [code_Customer Segment]-={'IC'}>}[Bill_Amount_aggr Entity]*LastYTDFlag_aggr)>0
                    and sum({< [code_Customer Segment]-={'IC'}>}Bill_Qty_aggr*LastYTDFlag_aggr)>0
                           and rangesum((Sum ({< [code_Customer Segment]-={'IC'}, Source={'Aggr_bill'}>}( Bill_Qty_aggr * CurYTDFlag_aggr ))
                           -
                                 Sum ({< [code_Customer Segment]-={'IC'}, Source={'Aggr_bill'}>}( [Bill_Qty_aggr] * LastYTDFlag_aggr )))
                                 /
                                        Sum ({<[code_Customer Segment]-={'IC'}, Source={'Aggr_bill'}>}( [Bill_Qty_aggr] * LastYTDFlag_aggr )))<20
                                        and
                                               sum({< [code_Customer Segment]-={'IC'}>}[Bill_Amount_aggr Entity]*CurYTDFlag_aggr)>0
                                                    ,rangesum(sum({< [code_Customer Segment]-={'IC'},%MaterialNumber_Key-={'0000000*'} >}[Bill_Amount_aggr Entity]*CurYTDFlag_aggr)
                                                     -
                                                            (sum({< [code_Customer Segment]-={'IC'},%MaterialNumber_Key-={'0000000*'}>}[Bill_Amount_aggr Entity]*LastYTDFlag_aggr)
                                                            /
                                                                   Sum ({< [code_Customer Segment]-={'IC'},%MaterialNumber_Key-={'0000000*'}>}Bill_Qty_aggr*LastYTDFlag_aggr)
                                                                   *
                                                                         Sum ({< [code_Customer Segment]-={'IC'},%MaterialNumber_Key-={'0000000*'}>}Bill_Qty_aggr*CurYTDFlag_aggr))),0)))

Thanks

Re: Reduce calculation time

lorenzoconforti — Wed, 06 Mar 2019 14:58:37 GMT

Does this expression actually work if you select a small subset of your data?

Re: Reduce calculation time

kylealpaugh — Wed, 06 Mar 2019 15:36:38 GMT

Yes it works if a filter is applied but not when calculating all of the data

Re: Reduce calculation time

Gysbert_Wassenaar — Wed, 06 Mar 2019 15:51:37 GMT

If you want to improve performance then you either need more cpu and ram or precalculate some things in the script and create some flag fields.

One thing you can do regardless is remove rangesums that only have one argument. Rangesum(sum(X)-sum(Y)) is the same as sum(X)-sum(Y). But it won't do anything noticable for performance I think.

Re: Reduce calculation time

hopkinsc — Wed, 06 Mar 2019 16:09:32 GMT

and there's always the option of adding a calculation condition to only display the data if <10000 (or whatever) rows are valid. This forces the user to make some selections first

Re: Reduce calculation time

Brett_Bleess — Wed, 06 Mar 2019 16:46:53 GMT

There is one other possibility that I wanted to throw out, increase the calc time, but I realize this may be counterproductive, but at least you can prove that it will calc if you give it the additional time. Unfortunately I am not a very good developer, so I will let those experts continue to chime in regarding ways to optimize things here.

See attached screenshot of QMC related to QVS settings. If you need to make the change in the Desktop Client, that is possible as well, in that case you just go to the Settings.ini for the client which by default will be located here:

C:\Users\UserProfile\AppData\Roaming\QlikTech\QlikView\Settings.ini

Edit the Settings.ini file and add the following under the [Settings 7} area:

ObjectTimeLimitSec=120

The value of 120 is double the default of 60, just FYI. Again, I know this is not how you wanted to do things, but at least you can confirm that the full calc will work if you give it the time, but I suspect you may run out of memory potentially, so do not be surprised if it switches to out of memory!

Regards,
Brett

Re: Reduce calculation time

lorenzoconforti — Wed, 06 Mar 2019 16:53:10 GMT

It seems pretty straight forward calculation; a few nested if statements but it should work fine. The only thing that might slow it down is this {'0000000*'}; try remove it and see if it improves.

How many rows of data are you going through? can you post a picture of the table structure? just to have a better understanding of how your data is organised

Re: Reduce calculation time

hopkinsc — Wed, 06 Mar 2019 17:00:02 GMT

if the '0000000*' values are not needed else where in any charts then it might be worth removing them in the script.

Re: Reduce calculation time

DataWrangler — Fri, 09 Sep 2022 11:10:05 GMT

Hi Kyle,

Neither QlikView nor Qlik Sense short-circuit. I other words, you're executing a whole lot more code than you think you are.

I'd recommend replacing the nested conditional with a dollar sign expansion. Rob W has a great article showing how:

https://qlikviewcookbook.com/2014/12/how-to-choose-an-expression/

Cheers!

Jeff