6 Replies Latest reply on Oct 6, 2018 11:03 AM by meenu choudhary

How to sum a calculate a field on top of another calculated field?

The current issue I have maybe a bit difficult to describe but I will do my best. Currently, in my workbook, I am experiencing duplicates at the product level so I created a calculated field to work around that. I wanted to know which individual products have not been quoted the past year so the answer should be 1 for not quoted and 0 for quoted, I worked around that by doing an if statement with % products not quoted the formula looks as such: IF [% Not Quoted] > 0 then 1 else 0 end which I named Prod Not Quoted. That worked great for me, however now I want do the count or sum(?) of products not quoted at the vendor level which would mean my products not quoted needs to be grouped by the vendor name. To be specific my objective is to provide a table of all products not quoted by vendor name without duplicates at the product level. What I tried to do is create a new calculated field using the previously which I calculated as the following: IF [Prod Not Quoted] = 1 then sum(1) else 0 end. The latter calculation, however, gave me the sum of all products quoted and not quoted along with the duplicates I am trying to avoid. Why would  IF [Prod Not Quoted] = 1 then sum(1) else 0 end not work?

• 1. Re: How to sum a calculate a field on top of another calculated field?

Hello Santiago,

Can you try with the below calculation?

SUM (IF [Prod Not Quoted] = 1 THEN 1 ELSE 0 END)

S

• 2. Re: How to sum a calculate a field on top of another calculated field?

Thank you but I am getting the error argument to SUM( an aggregate function) is already an aggregation and cannot be further aggregated. Do you think it is just a parenthesis issue or do you have any other suggestions?

• 3. Re: How to sum a calculate a field on top of another calculated field?

Hello,

Could you please provide some mock-up data ?

• 4. Re: How to sum a calculate a field on top of another calculated field?

It is a bit difficult due to the amount of data i am working with and the level of security of this data

• 5. Re: How to sum a calculate a field on top of another calculated field?

Thank you but I am getting the error argument to SUM( an aggregate function) is already an aggregation and cannot be further aggregated. Do you think it is just a parenthesis issue or do you have any other suggestions?

• 6. Re: How to sum a calculate a field on top of another calculated field?

Just attach some mock -up data.

Create a dummy files with few records.