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