topic Performance - Set Analysis vs IF Statements in QlikView

Performance - Set Analysis vs IF Statements

Wed, 24 Jul 2013 17:35:20 GMT

Hi Experts,

I have a huge reference application that has dashboards for pretty much all areas of our organization. I use this as a validation app to cross check numbers with other apps before releasing. This is one of the first app that I built and used if conditions in the expressions though out. It has 20+ sheets and have many charts in each sheet with multiple expression. Obiviously , the performance is terrible. I was thinking of replacing all the "if statements" with "set analysis".

So my question is , Is it worh spend time converting to "Set Analysis"? Does it improve performance significantly?

Example

Form :

sum(if( ORD_STATUS = 'Complete' AND Approval ='Y' AND Type = 'Web Sales',(if(InYear(ORDER_DATE, (today()),-1), AMT)),0))

To:

sum({$<ORD_STATUS = {'Complete'}, Approval = {'Y'} ,Type = {'Web Sales'}, ORD_YR= {"$(=num(Year(Today(0))-1))"}, >}AMT)

Thanks,

Aji Paul.

Re: Performance - Set Analysis vs IF Statements

Gysbert_Wassenaar — Wed, 24 Jul 2013 17:49:09 GMT

It should improve performance. But significantly? Probably not since it's only one expression in one object. You can try to do some analysis to see which objects take a lot of memory and/or time. See for example this blog post: Recipe for a Memory Statistics analysis. Or the Document Analyzer application.

Re: Performance - Set Analysis vs IF Statements

Anonymous — Wed, 24 Jul 2013 19:01:38 GMT

In addition, the expressions with SET are not "cacheable". That means, they will be calculated every time.

Besides, it not always possible to use SET expressions in chart because the set is evaluated aginst all data, not per chart dimensions.

Regards,

Michael

Re: Performance - Set Analysis vs IF Statements

rwunderlich — Wed, 24 Jul 2013 22:57:26 GMT

My guess is that the SET version would peform must faster. The SET selection is done once for the whole chart. The IF() evaluates for every row of data.

-Rob

Re: Performance - Set Analysis vs IF Statements

Thu, 25 Jul 2013 16:23:52 GMT

Rob ,

Is there any way you could incorporate the expression CPU toll to your document analyzer ?

Thanks,

Aji Paul.