Maybe Set Analysis?

[#1]

I have a problem where an expression takes a long time to refresh when new selections are made. I have tried converting the expression with Set Analysis, but for some reason it does not give me a result. The expression is: Count(Distinct(IF(Trim(field1) = 'Some Condition', field2))). My second part of the question is: Is it Best Practise to use Set Analysis to speed up expression calculations or should I rather be doing the "complex" calculations in the load script?

[kaushiknsolanki · Accepted solution]

Hi,

     Let me tell you one thing, that Count() function is a time intensive, meaning it takes more time to execute then other functions.

     In you case it takes much longer time cause with count you have if statement.

     The equivelent set expression for your expression is.

     Count({<Field1 = {"Some Condition"}>}distinct Field2).

     This may take time too... as its a count function.

     The best practise is to put an extra field to table. say '1'.

     Now you can use the sum function on these field, and get count.

Regards,

Kaushik Solanki    

[#3]

Hi Kaushik - That was a good tip creating a new field: i.e. 1 AS [myCounter] and doing a Sum on it rather than using the Count clause. I also did the Trim part in the back-end and the refresh rate is now milli-seconds again apposed to 30 seconds - as it was when all the calculations and the Count was done in the expression on the front-end.

[kaushiknsolanki]

Hi,

    Thats good. From next time you wont get performance issue for sure - as you got the solution for that.

Regards,

Kaushik Solanki