Toad World® Forums

Calculated Field in Pivot Grid


#1

Let’s say that I have a two columns in Pivot Grid: Sales and Profit. I want to create a calculated field “Margin.” I click the sigma button and create a calculated field: [Profit] / [Sales].

This works if the pivot grid is showing the part_number detail, but when the part_number if removed and I want to summarize by a department/category, then the calculation is wrong (it is just summing the margin percentages for each part_number that is within the department/category.

How can I set this calculated field to summarize correctly?


#2

I don’t know if I understand right. I am trying to reproduce it but I think it works fine. Can you send me more information as sample data?


#3

Here is an example: the dataset is at the part number level and each part is flagged as either being in the “Buy/Sell” or the “Make” group. The goal of the pivot is to aggregate on the Buy/Sell - Make groups. Summarizing the sales and profit are straight-forward…just drop them on the grid and they sum. Now I want to add a calculated column that would take the profit and divide by the sales to give the margin percentage. This is yielding margin percentages that are insanely high and it appears that Toad is calculating the margin at the part number level and then summing those values.

Instead, I need Toad to use the summed values of sales and profit.

In the attached example, I would expect the Buy/Sell record to be 224752 / 1093604 = 20.55%
PivotCalculations


#4

I tried to reproduce your example on our test DB. I have this data:
data
If I make sum column TOTAL_PRICE then it must equal AMOUNT_BILLED.
But in Pivot it is not correct as you see:
pivot1
Problem is aggregations in column AMOUNT_BILLED. Use other aggregation (average, min, max) and data will be correct.
pivot2


#5

That seems to work for that scenario, but I’m trying to perform a calculation and it does not work. Try an example similar to mine, please…

(Price_Customer_Extended / Profit_Extended)

or

( (Price_Customer_Extended - Cost_Extended) / Price_Customer_Extended )


#6

I have calculation ([TOTAL_PRICE])/([AMOUNT_BILLED]) in column “Sum of Percent”


#7

Raw data:
Raw%20Data

Two parts sold that are in the “Clamps and Fasteners” department and in the “Buy/Sell” category. The margin for each part is (Profit Extended / Price Customer Extended). Calculated for each part would be:
NTE-04-LACC19 : 12.07 / 19.95 = 60.50%
NTE-04-LACC39 : 92.82 / 161.82 = 57.36%
Overall for the department and category: 104.89 / 181.77 = 57.70%

But when I use the pivot, it performs the calculation at the part level and then sums the percentages together ( 60.50% + 57.36% = 117.86% ). The margin in the pivot should be 57.70%.
Pivot%20Data

Changing the aggregation method to average for the margin column yields 58.93 (close but not correct), min yields 57.36, and max yields 60.50%.


#8

I finally reproduced it and I investigate now how to solved it.

Thanks
Petr


#9

Petr entered the issue as QAT-13586. We are wrapping up the 5.0 release so we won’t be able to fit that in so we will look at for the next release


#10

Thank you! Keep up the good work.