Loading ...

Posted in: QQube    Excel and Excel Add-In

Enterprise custom numeric field won't sum in pivot table

Subscribe to RSS
  • I have a custom numeric field in Enterprise.  In list view within QQube, it appears to be a text field.  When I create the Pivot table and drag this custom field into the Values section, I use the Value Field Settings option to change the Number Format to Numeric and Select Sum to summarize the field.  It doesn't work.  Every row in the Pivot table for this field now displays zero.  The individual values in each record are all less than .5.  (They are actually carats ... weights of diamonds.)

    I tried this by creating my own table of data and converting to a Pivot table, and it works fine.

    What am I doing wrong?  THANKS!!!

    Attachments
  • Lane,

    I just happened to come across your post.  How many decimals are you setting with your numeric setting? 

  • Hi Fran,

    Thanks for responding.  The number format I used for the custom field in Enterprise was "Numbers, any decimals"  In Excel, I was using the Number format with probably only 2 decimal places.  I just changed that to 6 decimal places, but still get zeros, i.e. 0.000000

  • All custom fields in QB are text.  You assign a format to them inside of QB, but they are still stored and extracted as text.

    If you are using pivot tables, you need to create a calculated field that turns text into numbers.  = VALUE ( Text )

    If you are using PowerPivot Tables, then go into the data model, and format the field as numeric - this will actually create a numeric field.  Normal Excel, trying to do that will not work.

    Here is some additional information for you: https://clearify.com/wiki/view/201/custom-fields

Page 1 of 1 (4 items)