Friday, July 29, 2011

A Chart Expression That Works Differently for Totals Row


I built a QlikView document last week with a straight table chart where I needed to have some of the expressions work differently on the totals row than on the regular rows. One of the dimensions on the chart was date -- but the totals row needed to aggregate data for the most recent date only. I wrote an expression that takes advantage of the fact that the RowNo function returns null for the totals row. So, leaving out the actual calculation part, the expression looked something like this:
if(IsNull(RowNo()),
/* do the totals row calculation */ ,
/* else, do the regular calculation */)


A similar idea can be used if you want to change the color or bold the totals row. For example, you could use an expression like this for background color:
if(IsNull(RowNo()),yellow())

and make the totals row look like it is emphasized with a highlighter color.

Important note: If you use the RowNo() function in your column expression you will not be able to re-sort the chart by double-clicking on a column title. If that is a problem for your application then use if(Dimensionality()=0,... as a substitute.


* * *

6 comments:

Deepak said...

Smart & very useful tip. Thanks for sharing.

Luke said...

I found that in QV9SR6, the Totals row returns RowNo() = 0 and is not null. This is with the Totals row at the top. I haven't attempted to place it on the bottom of the straight table.
Thanks for this tip though, saved me a week of module coding and simplified my application.

McNater said...

Awesome...great tip thanks. Worked like a charm for highlighting totals for me.

kakajacksparrow said...

Great tip...came across this just in time...saved me a lot of effort...thanks Pritam

Anonymous said...

I am using a straight table with a representation of "Linear Gauge". I want to have a different color for the Totals row and non of the examples above seems to work.
I tried RowNo() = 0
Dimensionality()=0
IsNull(RowNo())

Can you suggest any idea?

-TB said...

I think you should post your linear gauge question to QlikCommunity. It will put more eyes on it than you can get here. Good luck.
-Tim