Number/Data Formatting Within Pie Charts.

[#1]

Howdy QV Community (First time poster..)

I have a bit of an issue, i'll give a quick background to what I am trying to do before I start posting code and screaming "Help!".

We are using QV to report financial details, on each sheet we have an object that scales the data by factors of [1,1000,1000000]. These are saved in a variable called vScale. To avoid 'hard-coding' values and messy 'If' statements we are using the variable vScaleFactor to adjust values to their correct value.

This is all working correctly with the current code:

=num(sum(if([Fisc Year]=2010,[PBIT Impact]*$(CX),0))/ vScale,'$(vScaleFormat)')

Now this is where the fun begins...

Our requirements are that when vScale = 1 then all values should be whole numbers (No decimal places). However when vScale = 1000 or 1000000 then it must be correct to 1 decimal place.

We did this by creating an 'If' statement in vScaleFormat

=If(vScale=1,'#,##0','#,##0.0')

This works for Text Objects and Captions and Labels... But not with the charts themselves. The main problem being that we have a Pie Chart on the report that displays these values (Correctly I might add...) yet when we change to a vScale=1 there is still one decimal place. I cannot remove this one decimal place for the life of me.

Losing sleep over it!

"Help!!!"

[jagan · Accepted solution]

hi,

Use this example for ur requirement

NUM(Expression, if(vScale=1,'#,##0', '#,##0.0'))

and set Chart Properties->Number->Number Format Settings to Expression Default.

[#3]

Thank you very much for your reply;

Unfortunately this expression is not producing the results expected. The correct values are produced but the statement to determine the decimal places does not seem to have any effect.

I did ensure to change the Number Format Settings to Expression Default and I did try testing it with different formats (ie: #,##0.00 and other variations). I am a little confused as to why it will not work since it looks like it should.

[#4]

I thought I should add the code that I tried so anyone can pick an error:

NUM(Sum(If([Year]=2010,[Impact]*$(CX),0)/ vScale),If(vScale=1,'#,##0', '#,##0.0'))

[#5]

Alright.. maybe this is fixed in 9.0... but as far as I can tell -

No matter what expression I use, even if a very simple expression simply to set decimal places it does not alter the value. It seems the only way I can do anything with it is to 'hard' set the decimal place in the Number tab.

[wizardo]

hi Kieran,

i have tried this both in QV9 and QV8.5

i have tried using 1 variable and two variable

i have tried hardcoding the if in the chart

i even checked if using/not using the $ expension makes a diffrence

IT ALL WORKED!!!

so maybe there is somthing else thats wrong

if you could post a sample maybe we can trace it.

try reversing the if (making it <>1 instead of =1).

where does the 1 come from? do the user input it in an input box? slider? script field?

maybe it isn't equal to 1? - just a wild guess

Mansyno

[#7]

Thanks for the assistance so far Mansyno, unfortunately I have had no success with your suggestions. I will however explain and clarify where the numbers are coming from for you.

vScale is a Variable we use in an Input Box on the sheet. The values in this box are [1,1000,1000000].
vScaleFormat is another Variable that has the following code.

=If(vScale=1,'#,##0','#,##0.0')

I have tried just writing the 'If' statement in the expressions but it produced the same result so I just use the vScaleFormat variable to keep it clean.

I can't view my first post but I use a Text Object to display a total for the graphs and I have used the following code to produce the exact result I want, however it doesn't work in the chart.

=num(sum(if([Year]=2010,[Impact]*$(CX),0))/ vScale,'$(vScaleFormat)')

Note: CX is our currency variable. Again, an Input Box dictates its value.

Obviously this is a bit frustrating that it will work in a Text Object but not my chart, so I hope this clears up where the '1' comes from and what the other variables in my code are.

[#8]

I feel a little stupid here - Tell me if I am right...

I went back to square one and started by using the original code that I had...

=Num(Sum(If([Year]=2010,[PBIT]*$(CX),0))/vScale,$(vScaleFormat))

Then accdiently left the 'Text on Data Point' box checked instead of changing it to 'Numbers on Data Point'..... and it worked... I tested this on our development server and locally because I thought I was going mad. I thought that the obvious property for this field would be Number but it only works if selected as Text...

Am I right or did I fluke it somehow?