I recently diagnosed a slow Straight Table chart. The chart contained 100K+ rows. One column contained a complex expression that returned a Y/N string flag for the column. Something like:
=if(complex expression, ‘Y’, ‘N’)
They also wanted to set the background color of the cell, green for Y, red for N. So the Expression Background Color property repeated the same complex expression to assign a color:
=if(complex expression, green(), red())
I surmised the expression was being calculated twice for each row. I changed the main expression to set a Dual().
=if(complex expression, dual(‘Y’,1), dual(‘N’,0))
The chart cell still displays the Y/N text. But now I could use 1 and 0 values on the Visual Cues pane and eliminate the Background Color expression entirely. Much faster!
-Rob
Nice!
All color functions in QlikView return values that can be interpreted as numbers. As such, you can use the dual function to encode the colors directly in the field’s values. This allows you to forgo any logic for deriving the color on the front end at all, instead just referencing the field itself as the formatting expression.
Ex:
=if(complex expression, dual(‘Y’,green()), dual(‘N’,red()))
@Mike
That’s an interesting idea. I tested it and it actually performed worse than the original example of using the complex expression.
I assigned the color in the Dual() and then used the Expression label
=MyExpr
as the Text color expression.
I have a couple of thoughts as to why this would perform worse, but I’ve wondered if you can replicate those results?
I think Visual Cues will always outperform Expression Attributes.
Didn’t HIC wrote on the design blog some time ago not to use visual cues?
But I can see the benefits in this case, dual and visual cues should be the best performing one.
I’ll just have to find the time to do some performance testing on different color coding techniques.
Normally when I encode colors in field values, I am doing it in the data model rather than dynamically on the front end, which is always faster.
I’m not sure why one would perform better than the other in this case, however. It seems like there should be one fewer thing to evaluate (only one expression, total), forgoing the Visual Cues entirely.
I don’t recall seeing any advice to avoid Visual Cues. My testing is that it’s generally faster than color expressions.
Took the time for some performance testing. Dual and visual cue outperforms the other ones.
http://yaqvblog.wordpress.com/2014/05/07/color-coding-cell-in-straight-table/