topic Re: Formulate dimension based on accumulation of expression in QlikView

Formulate dimension based on accumulation of expression

Fri, 30 May 2014 08:35:28 GMT

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).

Re: Formulate dimension based on accumulation of expression

Anonymous — Fri, 30 May 2014 08:49:44 GMT

Post you sample app or some data in excell

Re: Formulate dimension based on accumulation of expression

Fri, 30 May 2014 08:57:48 GMT

Sure, sample added.

Re: Formulate dimension based on accumulation of expression

tresB — Fri, 30 May 2014 08:58:45 GMT

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

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

Re: Formulate dimension based on accumulation of expression

Fri, 30 May 2014 09:04:10 GMT

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.

Re: Re: Formulate dimension based on accumulation of expression

tresB — Fri, 30 May 2014 10:32:08 GMT

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.

Re: Re: Formulate dimension based on accumulation of expression

Fri, 30 May 2014 11:08:38 GMT

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.

Re: Re: Formulate dimension based on accumulation of expression

tresB — Fri, 30 May 2014 11:13:55 GMT

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())