topic Number/Data Formatting Within Pie Charts. in QlikView

Number/Data Formatting Within Pie Charts.

Fri, 26 Feb 2010 06:43:46 GMT

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!!!"

Number/Data Formatting Within Pie Charts.

jagan — Fri, 26 Feb 2010 08:16:51 GMT

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.

Number/Data Formatting Within Pie Charts.

Sun, 28 Feb 2010 23:19:13 GMT

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.

Number/Data Formatting Within Pie Charts.

Mon, 01 Mar 2010 01:02:06 GMT

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'))

Number/Data Formatting Within Pie Charts.

Mon, 01 Mar 2010 03:00:07 GMT

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.

Number/Data Formatting Within Pie Charts.

wizardo — Mon, 01 Mar 2010 07:05:12 GMT

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

Number/Data Formatting Within Pie Charts.

Mon, 01 Mar 2010 23:25:44 GMT

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.

Number/Data Formatting Within Pie Charts.

Tue, 02 Mar 2010 05:47:09 GMT

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?