Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
I am here by sending you the sample
Regards,
Kaushik Solanki
Maybe try if(sum(sin_Sales) = 0, '', sum(sin_Sales)) as the main expression instead of using the color.
Hi,
I have attched the sample doc.
Have a look
Hope this will help you
Regards,
Kaushik Solanki
Thanks - I have ameded your sample to reflect what I am doing...
This highlights the issue....
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
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
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.
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
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 #,###;-#,###;-
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?