topic Re: Pivot Total Row - Colour in QlikView

Pivot Total Row - Colour

Wed, 07 Dec 2016 12:45:01 GMT

Hi,

I am conditionally formatting the background colour of a pivot table :

Is it possible not to colour the 'Totals' rows ?

Thanks for any help

Re: Pivot Total Row - Colour

vinieme12 — Wed, 07 Dec 2016 13:02:57 GMT

Are you using an expression to calculate the background? please post

https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Custom_Format_Cell_dialog.htm

Re: Pivot Total Row - Colour

Wed, 07 Dec 2016 13:59:54 GMT

Hi yes I am,

if(sum(final_total)>=8, rgb(255,0,0),

if(sum(final_total)<3.0, rgb(187,255,187),

if(sum(final_total)>=3.0 and sum(final_total)<=5.0, rgb(255,255,157),

if(sum(final_total)>=6.0 and sum(final_total)<=8.0, rgb(255,128,128)))))

Thanks,

Re: Pivot Total Row - Colour

c_gilbert — Wed, 07 Dec 2016 14:01:52 GMT

Try this instead:

=if(RowNo()<>0 and ColumnNo()<>0,

if(sum(final_total)>=8, rgb(255,0,0),

if(sum(final_total)<3.0, rgb(187,255,187),

if(sum(final_total)>=3.0 and sum(final_total)<=5.0, rgb(255,255,157),

if(sum(final_total)>=6.0 and sum(final_total)<=8.0, rgb(255,128,128)))))

)

Re: Pivot Total Row - Colour

vinieme12 — Wed, 07 Dec 2016 14:03:11 GMT

Try

if(rowno() <> 0 ,

if(sum(final_total)>=8, rgb(255,0,0),

if(sum(final_total)<3.0, rgb(187,255,187),

if(sum(final_total)>=3.0 and sum(final_total)<=5.0, rgb(255,255,157),

if(sum(final_total)>=6.0 and sum(final_total)<=8.0, rgb(255,128,128)))))

,rgb(210,210,210)) // Gray for TotalRow

Re: Pivot Total Row - Colour

Wed, 07 Dec 2016 14:08:36 GMT

Hello ,

Both solutions work but only for the horizontal 'Hour' Totals at the bottom. Is there a solution for the Daily totals on the right hand side?

Many thanks,

Re: Pivot Total Row - Colour

vinieme12 — Wed, 07 Dec 2016 14:09:14 GMT

if(rowno() <> 0 OR ColumnNo()<>0,

if(sum(final_total)>=8, rgb(255,0,0),

if(sum(final_total)<3.0, rgb(187,255,187),

if(sum(final_total)>=3.0 and sum(final_total)<=5.0, rgb(255,255,157),

if(sum(final_total)>=6.0 and sum(final_total)<=8.0, rgb(255,128,128)))))

,rgb(210,210,210)) // Gray for TotalRow

Re: Pivot Total Row - Colour

Wed, 07 Dec 2016 14:12:11 GMT

Hi Vineeth,

Thanks for your prompt reply! That only seems to alter the bottom right corner cell ...

Any ideas?

Re: Pivot Total Row - Colour

vinieme12 — Wed, 07 Dec 2016 14:24:21 GMT

if(rowno() = 0 , lightgray(), if(ColumnNo()=0, lightgray(),

if(sum(final_total)>=8, rgb(255,0,0),

if(sum(final_total)<3.0, rgb(187,255,187),

if(sum(final_total)>=3.0 and sum(final_total)<=5.0, rgb(255,255,157),

if(sum(final_total)>=6.0 and sum(final_total)<=8.0, rgb(255,128,128)))))

))

removed an extra column at end

Re: Pivot Total Row - Colour

Wed, 07 Dec 2016 14:26:43 GMT

perfect - many many thanks!