Document numbers default to currency

[#1]

I have a sales pipeline application that tracks numbers in both dollars and numbers. I have an inline table that someone else built that toggles the viewed data from $ to #. When the data is shown as dollars, it displays correctly, but when toggled to show plain numbers, as shown in the screenshot, I get the $ in front of the numbers no matter what. This happens not only on most of the sheet objects but most of the document objects regardless of tab.

Am I missing something? Is there somewhere the "default' number format for the document has been changed to currency instead of fixed numbers?

[#2]

Just a guess, but since the character '#' is used in formats, it could mean selecting it does nothing to change the current format.

Have you tried this with a character that isn't a native QV char ... something like a '\' or '~'?

[#3]

Actually this had been working fine for the past year until we upgraded everything from 8.5 to 9 at the server. then suddenly I had some issues with number formatting. The individual who created the application is on leave for another month, but says this in a recent email:

if(pipecharttype='$',num(sum(pipelinevalue),'$#,###;($#,###)'),num(sum(pipelinecount),'#,###;(#,###)'))

notice the dollar sign for the pipelinevalue and no dollar sign in the pipelinecount. Did this somehow get changed? Or… is the number format set on "Expression default", if it got set to currency this may override the above expression.

I have not changed the pipelinecount statement, so I know it's not that. But her reference to "Expression Default" could be the culprit. I am assuming since this issue is showing on all sheets [entire document] that this might be it. But the help documentation is not too helpful with managment of document properties. Where do I find the overall setting for the number format for the entire document and check if it's set for "Expression default" as she suggests. Or is this not it?

My thanks in advance to everyone wanting to help. I would prefer not to rebuild something another has built and has been working until this past upgrade. The numbers display the right data, but even with fixed numbers I end up with a $ in front of everything by default. Has to be a document setting somewhere?

[johnw]

OK, that looks like what I'd expect. In regards to "Expression Default", go to the chart properties, Number tab, and select that expression. There is a list of options to the right of it, and the top option is "Expression Default". That must be selected, NOT currency. Also, you must NOT have a $ symbol in the symbol box. The symbol box should be empty.

In case some of it is relevant, here's what I had already written when I saw your response:

Data is loaded in with a default format. There are a LOT of places that this format can be overridden. So what you're trying to look for is WHERE the original author was trying to override with either $ or #. I'd have probably done it in the expression in the chart. Something like this:

num(count(SomeField),if("Select to view by"='$','$#,##0.00','#,##0'))

Another place to look might be on the Number tab for the chart properties. Is there some expression entered for the symbol? That might be a simpler way to handle it if all you're trying to do is put a dollar sign in front or not rather than adding decimal places and parentheses for negatives. Something like this, I think:

if("Select to view by"='$','$')

There are probably other places it could be hiding as well.

[#5]

Okay, now I'm getting somewhere. I went into each object not displaying correctly and found the format was showing "money". I moved it to Expression Default and so far so good. Except that now the dollar amounts in many objects show as [for example] $5,737e+005.

I checked the format of the code, and the only way I could change the above to display properly was to change her format code from $#,### to $#,###0

If I left the 0 off as she had it, I get the oddball e+005 added to the data result. The final issue I had was larger dollar amounts not showing the comma in the correct spot. I don't understand why but $#,###0 would render $54,000 as $5,4000 unless I changed it to $##,##

These changes have helped all but one object that for some reason now refuses to display currency at all.

[johnw]

I think you want $#,##0 and #,##0 as the formats, assuming the values are always positive. I'm pretty sure the 0 just says to display that digit when it is available. I wouldn't have thought that $#,### would display in scientific notation, but since it is, that's a good excuse to change it.

I haven't experimented with currency formats in them, but stacked bar charts may behave differently than regular bar charts. Is there just one expression for the stacked bar chart? What's the expression?

[#7]

=IF(pipecharttype='$',Time & ' Value by Market or Person',Time & ' Count by Market or Person')

and

IF(pipecharttype='$',num(sum(if(closestatus='Won',actualvalue*$(vSelectedFlag),estimatedvalue*$(vSelectedFlag))),'$##,##0;($##,##0)'),num(count(if($(vSelectedFlag)=1,opportunityid)),'#,###;(#,###)'))

As to the explanation why $#,### returns a number with the comma in the wrong spot, that one puzzles me. But $#,### returns a number like $5,2000 . I can only get the comma to display in the right spot if I format like this: $##,## and then it will display as $52,000. I don't have an answer for that as it makes no sense. But I appreciate all the help as "most" of the issues were resolved but there is still some pretty odd things going on.

Again, huge appreciation for the help. I sort of inherited someone else's work and reverse engineering what was done after the upgrade of the server from 8.5 to 9 impacted number formats has been a real pain.

[johnw]

Hmmm, not seeing why that expression wouldn't show the $ when selected. Also no idea why $#,### would return a numer like $5,2000. Sorry!