topic Re: Adding conditional formatting to a pivot table that uses ValueLoop in QlikView

Adding conditional formatting to a pivot table that uses ValueLoop

LizWhit — Mon, 07 Sep 2020 12:37:40 GMT

I have a pivot table where I have used ValueLoop to get the layout correct as a simple pivot table did not return all the data. However, I would like to add conditional formatting (change the background colour) depending on the value of a field which is not in the pivot table. Is this possible?

Thanks

Re: Adding conditional formatting to a pivot table that uses ValueLoop

Kushal_Chawda — Mon, 07 Sep 2020 14:29:26 GMT

@LizWhit what is the expression?

Re: Adding conditional formatting to a pivot table that uses ValueLoop

LizWhit — Tue, 08 Sep 2020 06:24:08 GMT

@Kushal_Chawda thanks for replying. The expression is:

=subfield(concat(distinct [Bay / Bed],','),',',valueloop(1,25))

There are two dimensions, [Bedstay Ward] and =valueloop(1,25) and I would like the background of the [Bay / Bed] cell to change based on the value of another field called Status.

Thanks

Re: Adding conditional formatting to a pivot table that uses ValueLoop

Kushal_Chawda — Tue, 08 Sep 2020 13:19:58 GMT

@LizWhit so what is the logic to color based on status?

Re: Adding conditional formatting to a pivot table that uses ValueLoop

LizWhit — Tue, 08 Sep 2020 13:25:33 GMT

@Kushal_Chawda

=if(Status = 'Positive ',Colour_Red,if(Status = 'Past',Colour_WebsiteBlue, if(Status = 'Negative ',Colour_Green etc etc.

thanks

Re: Adding conditional formatting to a pivot table that uses ValueLoop

Kushal_Chawda — Tue, 08 Sep 2020 15:09:05 GMT

@LizWhit Would you be able to share a sample data with expected output?

Re: Adding conditional formatting to a pivot table that uses ValueLoop

LizWhit — Tue, 08 Sep 2020 15:35:44 GMT

@Kushal_Chawda this is a screenshot of the report as it stands

The field to the left is the Dimension [Bedstay Ward] and the cells to the right is the Expression with the value loop. I would like the cells to change colour depending on the status. Below is a simple pivot table with the same fields where the formatting works, but it is not the layout that I want:

Thanks

Re: Adding conditional formatting to a pivot table that uses ValueLoop

Brett_Bleess — Tue, 22 Sep 2020 17:18:56 GMT

Perhaps the following Design Blog post may be helpful:

https://community.qlik.com/t5/Qlik-Design-Blog/Colors-in-charts/ba-p/1475280

Regards,
Brett

Re: Adding conditional formatting to a pivot table that uses ValueLoop

LizWhit — Thu, 24 Sep 2020 08:24:09 GMT

Thanks for this Brett, it is certainly useful to streamline the use of colours, but unfortunately it does not resolve my particular issue. I suspect what I am trying to do is not doable. I am using a workaround of multiple pivot tables (one for each [Bedstay Ward]. Not ideal because it doesn't pick up new values, but the best option I have found. Thanks

Liz