Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pkelly
Specialist
Specialist

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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

   I am here by sending you the sample

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

17 Replies
kji
Employee
Employee

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    I have attched the sample doc.

    Have a look

   Hope this will help you

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
pkelly
Specialist
Specialist
Author

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

This highlights the issue....

matt_crowther
Luminary Alumni
Luminary Alumni

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
pkelly
Specialist
Specialist
Author

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.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable

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
Specialist
Specialist
Author

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?