8 Replies Latest reply: Apr 29, 2014 10:27 AM by Greg Williams

# Which expression will perform well in chart

Hi All,

I have two ways to write a expression in Chart. I want to calculate Sum of duration against a study dimension. I am using date dimesion as a data island.

Which expression will perform well out of the two written below:

1. sum(if(MonthYear=#MONTH_YEAR,Duration))

2. sum({<MonthYear={\$(=concat(#MONTH_YEAR,','))}>} Duration)

The dimension in chart is StudyId.

MonthYear column is a number represent a unique number for each month and year. This column is part of Study table.

#MONTH_YEAR is also a unique number for each month and year. It is part of Calendar table.

Hope I am able to explain my problem clearly.

Thanks & Regards,

Anosh Nathaniel

• ###### Which expression will perform well in chart

I may be mistaken, but in my opinion I think using your first opcion mighte better for performance because in option 2 your are not just evaluating with set analysis but also concatenating, perhaps  is better to use an "if" rather than using option 2., but thats just my guess.

• ###### Which expression will perform well in chart

Definitely the Set Analysis expression will perform better, despite the CONCAT function.

In the first expression, not only the IF function is really heavy, but QlikView will have to build a Cartesian Join between your "Fact" table and the "Date Island" table (multiplying all possible values from both tables) in order to calculate an expression like this.

If you could avoid the Date Island and link the calendar by date, that would be even better. If you can't (usually because of the multiple date fields in the app), then use Set Analysis only for better performance.

cheers,

Oleg

• ###### Which expression will perform well in chart

well then, I guess I can learn something every day, didn't expect that option should be faster

• ###### Which expression will perform well in chart

Hi Oleg,

If i create variable for calculating concat(#MONTH_YEAR,',')  and then use that variable in the expression, will it make some difference in performance?

Regards,

Anosh

• ###### Which expression will perform well in chart

Keep in mind that the dollar expansion will only be performed once in the set expression, and not on each row. So the expected result from the expansion is on the total data set, which might not always be what you are looking for, eventhough with your data island it will not make a difference.

Since it it only expanded once, there should be no major performance gain to use a variable instead.

• ###### Which expression will perform well in chart

Hi All,  I partially got my answer but just keep this thread open for some more experts opinion. Thanks all for your quick reply.

• ###### Re: Which expression will perform well in chart

Anosh - have you tried creating the expressions/ logic and turn on Memory statistics to assess the difference in performance?

• ###### Re: Which expression will perform well in chart

The second Expression will work very fast when compare to first because in set -analysis if we use if condition it will be very slow because it excute the whole loop and each and every row of data