topic Re: Conditional Text Formatting on Pivot Table in QlikView

Conditional Text Formatting on Pivot Table

Tue, 15 Jul 2014 19:19:13 GMT

I'm trying to do a conditional formatting on a Pivot Table where one of the dimensions is the Months. I'd like to have the past months to be in bold font. Somehow, it's partly doing it with this:

if(Month(EffectiveDate) < Month(Today()), '<b>'))

It works partly because in some selections, some (yes, just some) of the past months is not showing as bold. Not sure what I'm missing.

Please help! I'm a newbie!

Re: Conditional Text Formatting on Pivot Table

Anonymous — Tue, 15 Jul 2014 20:06:49 GMT

today() returns the date of last script reload and not exactly today's date. you need month(today(1))

Re: Conditional Text Formatting on Pivot Table

Tue, 15 Jul 2014 20:11:25 GMT

I changed it but it still has the problem. Thanks for the input anyway.

Re: Conditional Text Formatting on Pivot Table

Tue, 15 Jul 2014 20:24:58 GMT

I noticed that it's not changing to bold if the the value of the first row is zero. Mar and Apr should be bold. Jul to Dec is correct as regular.

Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
21	(13)	-	-	9	15	6	6	6	6	6	6
(49)	(35)	(25)	(25)	(21)	(33)	(55)	(62)	(62)	(62)	(62)	(62)
138	99	85	83	72	26	156	156	156	156	156	156

Re: Conditional Text Formatting on Pivot Table

Anonymous — Tue, 15 Jul 2014 20:25:26 GMT

no problem.

did you try adding a column for Month(EffectiveDate) and may be even one for EffectiveDate to see what value it is returning for the rows where the past months are not showing as bold?

Re: Conditional Text Formatting on Pivot Table

Tue, 15 Jul 2014 20:29:31 GMT

I noticed that it's not changing to bold if the the value of the first row is zero. Mar and Apr should be bold. Jul to Dec is correct as regular.

Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
21	(13)	-	-	9	15	6	6	6	6	6	6
(49)	(35)	(25)	(25)	(21)	(33)	(55)	(62)	(62)	(62)	(62)	(62)
138	99	85	83	72	26	156	156	156	156	156	156

Re: Conditional Text Formatting on Pivot Table

Tue, 15 Jul 2014 20:31:22 GMT

If i switched the first and second rows, then the Mar and Apr becomes bold and solves the problem. But I don't want to switch rows. It seems like it's basing it on the values of the first row.

Re: Conditional Text Formatting on Pivot Table

Anonymous — Tue, 15 Jul 2014 20:47:50 GMT

you think you can post a sample qvw?

Re: Conditional Text Formatting on Pivot Table

Tue, 15 Jul 2014 20:51:09 GMT

it's kinda tough to create a sample since there's a lot of tables basing it from. makes me thinkg that the problem may lie in the model.

Re: Conditional Text Formatting on Pivot Table

maxgro — Tue, 15 Jul 2014 20:53:24 GMT

maybe (to check, add 2 expression, month(...) and max(month(...)) to see if there is any difference)

if(max(Month(EffectiveDate)) < Month(Today()), '<b>'))

Re: Conditional Text Formatting on Pivot Table

Tue, 15 Jul 2014 20:57:52 GMT

no difference. still the same.

Re: Conditional Text Formatting on Pivot Table

maxgro — Tue, 15 Jul 2014 21:07:00 GMT

and if you add 2 expressions

max(Month(EffectiveDate))

Month(Today())

what do you get for Mar, Apr?

Re: Conditional Text Formatting on Pivot Table

Tue, 15 Jul 2014 21:38:25 GMT

10 and 11 for March and April, respectively.

Re: Re: Conditional Text Formatting on Pivot Table

Thu, 17 Jul 2014 16:49:46 GMT

Attached is an example of what I'm trying to do. the 'C' should be bold based on the conditional formatting I gave but since Q1 is zero for C, the Dimension is not showing bold.

Re: Re: Conditional Text Formatting on Pivot Table

gmoraleswit — Thu, 17 Jul 2014 17:08:45 GMT

You can have zeros instead of nulls, it seams like this solves the problem (see attached)

Re: Re: Conditional Text Formatting on Pivot Table

Thu, 17 Jul 2014 18:05:52 GMT

It works. Thank you. However, some of my Pivot Tables turn to all zeros after I un-select the 'Suppress Zero-Values'. I'll research on that next. Let me know if you know the answer to that as well.

Thank you.

Re: Re: Conditional Text Formatting on Pivot Table

Thu, 17 Jul 2014 18:12:53 GMT

Is there another way of accomplishing this. I have fields that are all zeros and I want to hide them.

Re: Re: Re: Conditional Text Formatting on Pivot Table

gmoraleswit — Thu, 17 Jul 2014 18:55:20 GMT

I couldn't find a nice way to do it, but what you can do is add another expression that populates the cells with 'ones' only for the first value/row (in case its always the same or you can calculate it someway)

See attachment