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

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

8 Replies
Not applicable
Author

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.

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

ToniKautto
Employee
Employee

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.

Anonymous
Not applicable
Author

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

Greg_Williams
Employee
Employee

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