Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Pivot Total Row - Colour

Hi,

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

pivot_totals_col.PNG

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

Thanks for any help

1 Solution

Accepted Solutions
vinieme12
Esteemed Contributor II

Re: Pivot Total Row - Colour

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

9 Replies
vinieme12
Esteemed Contributor II

Re: Pivot Total Row - Colour

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

Not applicable

Re: Pivot Total Row - Colour

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,

c_gilbert
Contributor II

Re: Pivot Total Row - Colour

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

)

vinieme12
Esteemed Contributor II

Re: Pivot Total Row - Colour

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

Not applicable

Re: Pivot Total Row - Colour

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,

vinieme12
Esteemed Contributor II

Re: Pivot Total Row - Colour

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

Not applicable

Re: Pivot Total Row - Colour

Hi Vineeth,

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

Capture1223.PNG

Any ideas?

vinieme12
Esteemed Contributor II

Re: Pivot Total Row - Colour

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

Not applicable

Re: Pivot Total Row - Colour

perfect - many many thanks!