Expression background color vs Custom cell format

sibrulotte — Thu, 03 Dec 2015 13:06:58 GMT

Hi,

I have an expression background formula that basically says if you're under budget by 250K and that is 10% of budget then put in lightgreen, if false but you're over budget by 50K and that is 10% of budget then you're in a red color.

see this:

=if((sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)-

sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)> 250000) and

(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)-

sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)>

0.1*(sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * %CR004M))),lightgreen(),

if((sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)-

sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)< -50000) and

(sum({$<[%CLE_Type_Transaction_ID] -= {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)-

sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * -1)<

-0.1*(sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Unité administrative ID]-={'0000'} >}[Montant cumulé aad] * %CR004M))), RGB(255,87,129)))

Works like a charm for all my rows except the last total line. It just free games on me

I try to fix the background color with a custom cell format, but the expression background color takes precedence.

Any clue why this would happen?

Re: Expression background color vs Custom cell format

marcus_sommer — Thu, 03 Dec 2015 13:36:56 GMT

I think the reason is that your expression on these TOTAL level returned no valid result. Probably you will need to wrap your expression into an aggr-function to get your desired result: AGGR...

- Marcus

Re: Expression background color vs Custom cell format

sibrulotte — Thu, 03 Dec 2015 14:01:38 GMT

Thank you for the hint.

I started a new expression to validate the proper AGGR nomenclature to obtain the desired effect.

With an AGGR on the three dimensions I am showing, I get a result for each line but blanks for totals.

So that's not working out for me right now.

topic Re: Expression background color vs Custom cell format in QlikView

Expression background color vs Custom cell format

Re: Expression background color vs Custom cell format

Re: Expression background color vs Custom cell format