Color intersection between calculated column and calculated row

How to color intersection between calculations?

When you do calculations in TARGIT (calculated rows and columns), you end up with intersections where these calculations “meet”.

Here’s a crosstab showing revenue per Salesperson by Quarters with a calculated column and a calculated row, where the intersection is left blank.

mceclip0.png

The calculated column just picks out the maximum value from all the columns with this syntax:
max(all, 0, m1)

The calculated row calculates the average per salesperson with this syntax:
avg(0, all, m1)

To fill the intersection, you have to decide which calculation should continue – in this case the result will be different depending on which calculation is continued.

In this case we continue the calculated column, by opening up the Max registration calculation and picking calculate in intersection.

Now we tick the Average – which means that the max calculation will continue in the average row:

mceclip1.png

Here’s the result – the max of the averages is picked out in the intersection cell:

mceclip2.png

So far so good.
Now we wan’t to color the Max registration column.

This can be done with a coloragent – just coloring all based on a condition that says all.

Here’s the result – and notice: the intersection cell is not included in the coloring.

mceclip3.png

 

Actually it’s not possible to color the intersection cell with a color agent.
It just can’t be reached this way.

So here’s creative approach – let’s to and change the crosstab theme for this particular crosstab.

PropertiesCrosstab Styling – coloring cells and alternating cells gives this result:

mceclip4.png

 

Now we have the intersection cell included in the coloring – so now we just have to make 2 coloragents (one that colors revenue, and one that colors average) that gives a white background to all the other cells.

mceclip5.png

And there we are – now the intersection cell is included in the coloring of the Max registration calculated column.

 

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.