Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

pkelly
Valued Contributor

Formatting text in expresssion

Hi All

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

CustomerMayApril
ASales0100
Margin050
BSales75100
Margin3550

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

CustomerMayApril
A100
50
BSales75100
Margin3550

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

1 Solution

Accepted Solutions

Re: Formatting text in expresssion

Hi,

   I am here by sending you the sample

Regards,

Kaushik Solanki

17 Replies
Employee
Employee

Formatting text in expresssion

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

Re: Formatting text in expresssion

Hi,

    I have attched the sample doc.

    Have a look

   Hope this will help you

Regards,

Kaushik Solanki

pkelly
Valued Contributor

Re: Formatting text in expresssion

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

This highlights the issue....

matt_crowther
Valued Contributor

Re: Formatting text in expresssion

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.

Blanks.PNG

Hope that helps,

Matt - Visual Analytics Ltd

Re: Formatting text in expresssion

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

pkelly
Valued Contributor

Re: Formatting text in expresssion

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

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

Not applicable

Re: Formatting text in expresssion

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 #,###;-#,###;-

pkelly
Valued Contributor

Re: Formatting text in expresssion

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?

Community Browser