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

Estimate Calculation

Vandana Samtani Oct 4, 2018 11:26 AM

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

Any suggestions would be helpful.

Thanks,

Vandana

Sample 1.xlsx 9.2 KB
Sample2.twbx 44.4 KB

1. Re: Estimate Calculation

maneesh.gaddam Oct 4, 2018 11:34 AM (in response to Vandana Samtani)
Vandana,

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

Sample2.twbx 38.1 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
2. Re: Estimate Calculation

Hari Ankem Oct 4, 2018 11:40 AM (in response to Vandana Samtani)
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.

Sample2.twbx 69.5 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
3. Re: Estimate Calculation

Vandana Samtani Oct 4, 2018 6:36 PM (in response to Hari Ankem)

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.

Thanks again for your help,
Vandana
Like Show 0 Likes(0)

Actions
4. Re: Estimate Calculation

Hari Ankem Oct 4, 2018 7:05 PM (in response to Vandana Samtani)
Attaching a 10.2 version.

Sample2_v10.2.twbx 73.3 KB
Like Show 0 Likes(0)

Actions
5. Re: Estimate Calculation

Vandana Samtani Oct 4, 2018 9:59 PM (in response to Hari Ankem)

Thanks Hari!

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

Thanks,
Vandana
Like Show 0 Likes(0)

Actions
6. Re: Estimate Calculation

Hari Ankem Oct 4, 2018 10:28 PM (in response to Vandana Samtani)

You need to show the formula you have written. Then only I can answer. Otherwise, the error message is pretty clear.
Like Show 0 Likes(0)

Actions
7. Re: Estimate Calculation

Vandana Samtani Oct 5, 2018 3:13 PM (in response to Hari Ankem)

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
Like Show 0 Likes(0)

Actions
8. Re: Estimate Calculation

Hari Ankem Oct 5, 2018 4:48 PM (in response to Vandana Samtani)

Do yourself a favor by attaching your packaged workbook here. I have no idea what you have written for Net Shipments.
Like Show 0 Likes(0)

Actions
9. Re: Estimate Calculation

Vandana Samtani Oct 5, 2018 5:54 PM (in response to Hari Ankem)
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

Sample 1.xlsx 9.3 KB

Sample3.twbx 12.4 KB
Like Show 0 Likes(0)

Actions
10. Re: Estimate Calculation

Hari Ankem Oct 5, 2018 6:18 PM (in response to Vandana Samtani)
Here you go: 2 different ways to do it.

Sample3.twbx 26.6 KB
Like Show 0 Likes(0)

Actions