Solved

Pivot Table does not recognize Negative Values as being negative

  • 23 September 2022
  • 6 replies
  • 1356 views
K
Rank
Userlevel 5
Badge +1

Hi All,

We recently fixed a data issue for one of our clients and we need to adjust some reporting on the Dashboards.
Basically, every other widget on a Dashboard recognizes negative values that are calculated as negative based on below Formula:
 


Yet when using this Data column from the Inquiry in the Pivot Table, it treats it as positive and adds to the total rather to substract from it.

What do I need to do that these values are recognized as negative by the Pivot Table?
It’s a very basic Table:

 



​​​​​​​Thank you!

icon

Best answer by BenjaminCrisman 23 September 2022, 22:15

View original

6 replies

BenjaminCrisman
Rank
Userlevel 7
Badge +4

Hi @krausef77 ! I’m not sure the issue is the pivot table per se, but that some values are stored as positive numbers even if their underlying intention is to reduce an amount.

Typically these will need to be accounted for by checking for the Doc Type and if it is a Credit Memo, then to multiply the result by -1.

In many GI situations this is the case and can be quick to overcome, but it might help to see a screenshot of the issue or post the GI here if this doesn’t resolve

K
Rank
Userlevel 5
Badge +1

Hi @krausef77 ! I’m not sure the issue is the pivot table per se, but that some values are stored as positive numbers even if their underlying intention is to reduce an amount.

Typically these will need to be accounted for by checking for the Doc Type and if it is a Credit Memo, then to multiply the result by -1.

In many GI situations this is the case and can be quick to overcome, but it might help to see a screenshot of the issue or post the GI here if this doesn’t resolve

Thanks for the quick reply.
Yes the formula is basically looking for specific RC Sales Orders (Return for Credit) aka they should be treated by the system as not adding to a Sales Total, but the Pivot table itself presents the values as positive rather than what is shown in the Inquiry. Here is a snippet of the Inquiry results based on the formula. Does exactly what it should and feeds as a negative value into other KPI widgets.
 

The Pivot table pulling the data from the same column with the same logic looks like this:
 

 

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@krausef77 This is because in the database they are stored as positive numbers and the pivots are grabbing the DB values it looks like, so that will need to be adjusted by multiplying -1.

Here is what I see in SQL:

So the UI is able to make this adjustment in some cases, but in other cases where a process is access the DB directly, then will need to use * -1 workaround

K
Rank
Userlevel 5
Badge +1

@krausef77 This is because in the database they are stored as positive numbers and the pivots are grabbing the DB values it looks like, so that will need to be adjusted by multiplying -1.

Here is what I see in SQL:

So the UI is able to make this adjustment in some cases, but in other cases where a process is access the DB directly, then will need to use * -1 workaround

Thank you Benjamin. 
Would I adjust the Generic Inquiry in a way that instead of “-[SOOrder.CuryOrderTotal]” to “[SOOrder.CuryOrderTotal] *-1” ?
Or where would I need to make the change?

BenjaminCrisman
Rank
Userlevel 7
Badge +4

@krausef77 You would need to make the adjustment in the GI to the line where the values populate.

If other order types will be visible, then you’ll need something like =IIF([SOOrder.OrderType]= ‘RC’, [SOOrder.CuryOrderTotal] *-1, [SOOrder.CuryOrderTotal])

K
Rank
Userlevel 5
Badge +1

@krausef77 You would need to make the adjustment in the GI to the line where the values populate.

If other order types will be visible, then you’ll need something like =IIF([SOOrder.OrderType]= ‘RC’, [SOOrder.CuryOrderTotal] *-1, [SOOrder.CuryOrderTotal])

Thank you so much for the quick answer! I wasn’t aware that the Pivot table looks at a little differently than other Dashboard widgets. 
Thanks again.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved