Help with improving performance of charts that's using a if statement

[#1]

Hi All

I currently use qlikview to do metrics reporting on our IT ticket metrics.  I have a chart that sums the number of tickets created and closed by month:

I have a table of master calendar information.  I have a table of all of the tickets.

The dimension of the chart is Calendar_MonthName.

The expressions are:

sum(if(Calendar_MonthName=MonthName(local_creation_date), pm_created_count))

sum(if(Calendar_MonthName=MonthName(local_closed_date), pm_closed_count))

for each ticket that's created, pm_created_count = 1

for each ticket that's closed, pm_closed_count = 1

I did that so I can do a sum which I understand is faster than a count

Everytime I load new data, it takes several seconds for the chart to process.  I like to improve the performance of the chart and I want to move the data into the load portion but I'm not sure how to do that.

Any suggestions?

[jagan]

Hi,

You can improve the performance by using the set analysis.  Use the expressions below

sum({<Calendar_MonthName={'=$(=MonthName(local_creation_date))'}>} pm_created_count))

sum({<Calendar_MonthName={'=$(=MonthName(local_closed_date))'}>} pm_closed_count))

Regards,

jagan.

[CELAMBARASAN]

Hi,

     Using Set analysis is always more performance in all places.

Try with this,    

sum({<Calendar_MonthName={'$(=MonthName(local_creation_date))'}>} pm_created_count))

sum({<Calendar_MonthName={'$(=MonthName(local_closed_date))'}>} pm_closed_count))

before that is local_creation_date and local_closed_date are field names?

Celambarasan

[Oleg_Troyansky]

Generally speaking, replacing IF with Set Analysis helps improving performance, however in this case Set Analysis can't be used, because Calendar_MonthName is used as a Dimension in the chart. The problem is that Set Analysis conditions are calculated once for the whole chart, therefore they can't be sensitive to the values of the individual Dimensions.

In your case, you need to find a solution within the dataset. One way or another, you need to transform your transactions in such a way that you could have a single Date field, and a single Month field associated with the Date, and 2 flags - CreatedFlag and ClosedFlag. It means that you might to double some of your data i order to achieve it.

This way, you will be able to count all the  "Created" and "Closed" by Month.

If you can't make such a big change in your dataset, then a small improvement can be achieved by replacing the alphabetic IF condition with a numeric one. While IF conditions inside aggregation functions are bad alltogether, the ones comparing numeric values are somewhat less heavy than the ones comparing alphanumeric values. Replacing MonthName with a numeric comparison, such as MonthStart date, can help performance. Also, avoid calculating MonthStart() function on the fly - make all the preparations in the load phase and only leave a simple comparison of two numbers in your chart expression.

Your performance would be improved much more if you could solve the issue in the data layer (the first suggestion of the two).

cheers,

Oleg

[#5]

Hi

I have redone my dataset and I created a new table with the Month as one column, then I have two more columns, Created is 1 if it was created on that date, and Closed is 1 if it was closed on that date:

Month          Created     Closed

12/2012      1              1

11//2012      1

However, I'm still using if statements in my Load statement

if(Month = MonthName(creation_date, 1) as Created,

if(Month = MontName(closed_date, 1) as Closed

Is there a way to improve the performance?  The if statements seem to be slowing down my loads.

[johnw]

This example has more to it than you probably need, but it demonstrates one way to connect multiple dates from multiple tables to a single master calendar.  The approach is additive, and doesn't require any changes to the original tables (unless it causes a loop).  I have not performance tested it, but would expect the chart performance to be good.  I make no promises about load speed, though.  Typically, I'm much more concerned with chart speed than load speed, though I try to optimize both where appropriate.