10 Replies Latest reply on Oct 5, 2018 6:18 PM by Hari Ankem

# Estimate Calculation

Hi All,

I need help in a calculation based on the logic below -

Calculation 1

Sum all Shipments when Code = 4,

Divide that value by all Shipments when code = both 4 & 5.

If this number is below 0.75, than make it equal 0.75.

Calculation 2

Example:

If total POS Units = 1000,000 Units

Net Ships with Code = 4: 800,000 Units

Net Ships with Code = 5: 200,000 Units

Net Ship Share = 800,000/(800,000+200,000)= 0.8

Estimated POS =  1,000,000/.80  = 125,000

Thanks,

Vandana

• ###### 1. Re: Estimate Calculation

Vandana,

Take a look at the attached and let me know if you have any questions.

1 of 1 people found this helpful
• ###### 2. Re: Estimate Calculation

Does this look right? Here are the calculated fields:

Calculation1:

IF

{SUM(IF [Code]=4 THEN [Shipments] ELSE 0 END)}

/

{SUM(IF [Code]=4 OR [Code]=5 THEN [Shipments] ELSE 0 END)}

<0.75 THEN

0.75

ELSE

{SUM(IF [Code]=4 THEN [Shipments] ELSE 0 END)}

/

{SUM(IF [Code]=4 OR [Code]=5 THEN [Shipments] ELSE 0 END)}

END

Estimated POS: {SUM([Units])}/[Calculation1]

Workbook is attached. Hope this helps.

1 of 1 people found this helpful
• ###### 3. Re: Estimate Calculation

Thanks guys!

I could not open either twbx file. Can you please reload again?

Also,  I got an error when I used the above formula. I also try using ifnull to replace null with 0 and still getting same error.

Vandana

• ###### 4. Re: Estimate Calculation

Attaching a 10.2 version.

• ###### 5. Re: Estimate Calculation

Thanks Hari!

Can you please let me know why am i getting the error in above screen shot?

Thanks,

Vandana

• ###### 6. Re: Estimate Calculation

You need to show the formula you have written. Then only I can answer. Otherwise, the error message is pretty clear.

• ###### 7. Re: Estimate Calculation

Thanks Hari!

Below is the code I have written and i fixed the error too.

The only issue is when i place this on a dashboard I do not see any results.

Can you please let me know what's happening?

Thanks,

Vandana

IF

(IF ATTR([code]) = 4 THEN ([Net Shipments]) END)/

(IF ATTR([code]) = 4 or ATTR([code]) = 5 THEN [Net Shipments] END)

< 0.75 THEN 0.75

Else

(IF ATTR([code]) = 4 THEN ([Net Shipments]) END)/

(IF ATTR([code]) = 4 or ATTR([code]) = 5 THEN [Net Shipments] END)

END

• ###### 8. Re: Estimate Calculation

Do yourself a favor by attaching your packaged workbook here. I have no idea what you have written for Net Shipments.

• ###### 9. Re: Estimate Calculation

Here you go.. Attached is the workbook.

I cannot share the actual data so created a sample file.

Net shipments is a calculated field.

sum([Gross Shipments])-sum([Returns])

The calculation below should be  3202500/3963500 ==> 0.80 Thank you again for your help,

Vandana