Skip to main content
Solved

Pivot Table does not recognize Negative Values as being negative

  • 23 September 2022
  • 6 replies
  • 1942 views

Forum|alt.badge.img+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!

Best answer by BenjaminCrisman

@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])

View original
Did this topic help you find an answer to your question?

BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+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


Forum|alt.badge.img+1
  • Semi-Pro I
  • September 23, 2022
BenjaminCrisman wrote:

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
Acumatica Employee
Forum|alt.badge.img+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


Forum|alt.badge.img+1
  • Semi-Pro I
  • September 23, 2022
BenjaminCrisman wrote:

@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
Acumatica Employee
Forum|alt.badge.img+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])


Forum|alt.badge.img+1
  • Semi-Pro I
  • September 23, 2022
BenjaminCrisman wrote:

@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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings