Number Formatting in QQube | CLEARIFY

I am formatting a QTY field as specified using the 'number formatting' instructions using the native Excel functionality.

The default quantity setting is showing five places after the decimal point.  I only want to see whole numbers and don't want to see all the zeros.  I have changed this multiple times and done everything I can think of, but it keeps changing back to showing a whole number followed by five zeros.  What am I doing wrong and how do I correct it?

I'm using QQube v5.5 and working in a pivot table with Excel 2013.

Hi Heath, What you might try is using the ROUND() formula. If you have a number like 457.12345.  If you want the nearest whole # with no decimals, try ROUND(457.12345,0).  Your result s/b 457.

Thank you for your reply.  I still have much to learn about Excel so please excuse my ignorance.  I don't know how to use a ROUND formula in a Pivot Table.  Am I correct in thinking this can only be done with List data or within Power Pivot?  I started the report from one of the QQube sample reports and don't know where the list data is.  Can I round the data by going into the data connection so it is rounded before it is pulled into Excel?

My understanding is that I should be able to right click on one of the cells in the QTY column, select Number Formatting, then format the numbers in the column so they are displayed in the nearest whole number.  When I do this the number keeps reverting back to displaying five decimal points.  I've done similar things in other reports and it has worked.  I'm trying to understand what is causing my problem and how to correct it.

I believe you said you are working with a pivot table, so you could skip the ROUND formula and just try this: Slowly drag your mouse down above your column until your cursor changes from a white cross to a black down-pointing arrow.  Then just left click and that should highlight your column.  Now you can right-select format cells.  From the Number tab, select Number and bring your decimals down to 0.

David or Darlene,

OK. . . after playing with it some more I realize now I was doing the right thing all along and you gave the right answer above.  However, it is still not working.  I see now I have other issues with data appearing incorrectly or not at all.  Not sure if it is a formatting issue on my part, an Excel issue or QQube issue. . .

By removing the QTY field from my report then adding it back to the report the rounding was working, but now I can't expand the last column of data (item description) so I can view it.  I then removed Item Description field from the report and 90% of my QTY numbers just went blank.

That is strange behavior Heath.  Maybe you should try to build a simple pivot table from 1 QQube Subject.  Pull in some fields to a pivot table and see if the behavior persists.  If not, perhaps you have a bad link to the source report you were using.

The article you reference should handle your formatting - as that is how it is done in an Excel pivot table (e.g. right click IN the pivot table column, value field settings>number format).  if you right click outside of the pivot table, your formatting won't work.  You MUST click within the pivot table itself, and in the right column.  I have not seen a case where this did not work.

Can you attach your spreadsheet please?

After reviewing the spreadsheet in a private case, it was because the qty field was in the labels area.  Anything you measure e.g. qty, amount, rate - should be in the values area of the pivot table.