topic Re: Formatting text in expresssion in QlikView

Formatting text in expresssion

pkelly — Thu, 19 May 2011 09:19:02 GMT

Hi All

I have a pivot table that looks something like...

Customer		May	April
A	Sales	0	100
	Margin	0	50
B	Sales	75	100
	Margin	35	50

Sales formula = Sum(sin_Sales)

GP formula = Sum(sin_GrossProfit)

What I want to do is show the zeros as a blank...

To do this, I visited the expresssion for each and keyed the following into the text colour property of the expression...

Sales .... =if(sum(sin_Sales) = 0, RGB(255,255,255))

GP........ =if(sum(sin_GrossProfit) =0, RGB(255,255,255))

What I end up with howver, is as follows...

Customer		May	April
A			100
			50
B	Sales	75	100
	Margin	35	50

i.e. If the customer has no sales in the most recent month, it blanks out the values but also blanks out the row descriptor...

Is this a bug or should I be doing something different to achieve what I want?

Thanks in advance for any help you guys can give...

Regards

Paul

Formatting text in expresssion

kji — Thu, 19 May 2011 09:59:02 GMT

Maybe try if(sum(sin_Sales) = 0, '', sum(sin_Sales)) as the main expression instead of using the color.

Re: Formatting text in expresssion

kaushiknsolanki — Thu, 19 May 2011 10:10:56 GMT

Hi,

I have attched the sample doc.

Have a look

Hope this will help you

Regards,

Kaushik Solanki

Re: Formatting text in expresssion

pkelly — Thu, 19 May 2011 10:15:59 GMT

Thanks - I have ameded your sample to reflect what I am doing...

This highlights the issue....

Re: Formatting text in expresssion

matt_crowther — Thu, 19 May 2011 10:21:29 GMT

Paul,

You could try a slightly different tack; place the control in the expression itself:

=if(sum(Sales)=0,'',sum(Sales))

That works for me.

Hope that helps,

Matt - Visual Analytics Ltd

Re: Formatting text in expresssion

kaushiknsolanki — Thu, 19 May 2011 10:27:12 GMT

Hi,

All you need to do is Go to Properties -> Visual Cues -> Set the upper limit or lower limit and change the text color to white

Regards,

Kaushik Solanki

Re: Formatting text in expresssion

pkelly — Thu, 19 May 2011 10:27:52 GMT

Thanks Matt, this was also suggested by Johan and it is more than likely be the route I will go down.

Am keeping open in case anyone else has a solution whereby i can use the text property...

This report is quite simple as far as the expresssion is concerned - I also have others where I would like to see zero values as blank boxes but these contain more complex statements and I would rather avoid adding more if statements to them.

Re: Formatting text in expresssion

kaushiknsolanki — Thu, 19 May 2011 10:28:00 GMT

Hi,

All you need to do is Go to Properties -> Visual Cues -> Set the upper limit or lower limit and change the text color to white

Regards,

Kaushik Solanki

Re: Formatting text in expresssion

Thu, 19 May 2011 10:29:49 GMT

Or you could use an Excel style format pattern in the Number tab of the properties box.

ie + format ; - format ; zero format ( you could leav the zero format blank or use as I do, a dash )

ie #,###;-#,###;-

Re: Formatting text in expresssion

pkelly — Thu, 19 May 2011 10:35:15 GMT

Thanks Kaushik

Upper >=0 Black text

Lower <= Red Text

I am already using the Upper and Lower limits...

Can you please advise what my settings would be for this?

Re: Formatting text in expresssion

kaushiknsolanki — Thu, 19 May 2011 10:41:46 GMT

Hi,

You want to show nothing when value is 0, then you should set the lower limit to 0 and text color to white.

Regards,

Kaushik Solanki

Re: Formatting text in expresssion

pkelly — Thu, 19 May 2011 10:48:18 GMT

I am possibly not understanding visual cues properly but beside the lower limit it states <= (please see screenshot below)...

I am using the Lower limit to force values below 0 to red text...

I changed the red to white and all my negative values disappeared.

Apologies if this is just my understanding...

Thanks for your help..

Paul

Re: Formatting text in expresssion

kaushiknsolanki — Thu, 19 May 2011 11:08:02 GMT

Hi,

I am here by sending you the sample

Regards,

Kaushik Solanki

Re: Formatting text in expresssion

pkelly — Thu, 19 May 2011 11:27:15 GMT

Thank you Kaushik can see what you meant now...

My understanding of visual cues is much better now as a result of this.

Thanks

Paul

Formatting text in expresssion

gandalfgray — Thu, 19 May 2011 12:12:15 GMT

Hi all

Kaushiks solution will be problematic if the numbers are not integers.

An alternative solution, that will work also for decimal numbers, is to use the Text Color attribute of the Chart column:

Formatting text in expresssion

kaushiknsolanki — Thu, 19 May 2011 12:20:20 GMT

Hi,

You are right. The solution we discussed is for number's only.

You have to decide what format your data will be in. and use the solution accordingly

Regards,

Kaushik Solanki

Formatting text in expresssion

pkelly — Thu, 19 May 2011 12:22:12 GMT

Hi...

This is where I started from but have an issue with my row labels...

Formatting text in expresssion

gandalfgray — Thu, 19 May 2011 12:26:10 GMT

OK, then maybe you can use the column() function: