Formulate dimension based on accumulation of expression

[#1]

I have a straight-table graph, with full accumulation in one of the columns.

I am trying to colour the background of the dimension depending on the value of the expression that has been accumulated. I can't seem to be able to do so. If I add an IF statement in the colour expression, I can't reference the accumulated value in the current row, nor can I use Above() or Column() to get expression values. Trying to use these functions gives me an error for that dimension.

Is there any way to base a dimensions (colour, background colour, text format) on the accumulated value of some expression? Is there any way of getting Above() and Column() functions working with a dimension?

Other than loading another column which works out an accumulated value on reload (which would mean the table must have a fixed order for it to make sense) I've got no idea where to go from here.

Here is a QVW to work with. Col1's background colour is currently based on the value of B, but I'd like it to be based on the accumulated value of B (B Accumulated in the table).

[Anonymous]

Post you sample app or some data in excell

[#3]

Sure, sample added.

[tresesco]

You can refer label of expression in the expression, like:

=If(YourLabel>100, green(), yellow())

[#5]

I tried =IF(BAccumulated,..,..) but that didn't work. Is there some special syntax for targeting an expression by it's label?

---

Sorry I replied before your edit. I want to reference the expression value from a dimension, not the expression itself.

[tresesco · Accepted solution]

Well, expression like:

=IF(Aggr(RangeSum(Above(Sum(Col2),0,count( total distinct Col1))),Col1)>100,Green(),Red())

PFA, and be careful of sort order as well, because that matters.

[#7]

Hi, this works really well, thanks!

Do I have to aggregate in order of A? Can I change the order of the aggregation?

Thanks again.

[tresesco]

If you change the sort order to Z->A, you have to use Below() ins tead of Above(), like:

IF(Aggr(RangeSum(Below(Sum(Col2),0,count( total distinct Col1))),Col1)>100,Green(),Red())