Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!"
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.
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.
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.
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'))
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.
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
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.
Note: CX is our currency variable. Again, an Input Box dictates its value.=num(sum(if([Year]=2010,[Impact]*$(CX),0))/ vScale,'$(vScaleFormat)')
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.
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?