8 Replies Latest reply on Oct 4, 2017 8:50 PM by Shinichiro Murakami

# Subtracting values from a total without resetting

I have a dataset where I need to subtract numbers from a total without "resetting" every time it reads a new row.

For each Month/Year/Country combination the formula should go:

(Number of Employees)*(160hrs/mo) - ((Visa Type 1 Completion Time)*(Count) + (Visa Type 2 Completion Time)*(Count) + (Visa Type Completion Time)*(Count))

This is equal to:

(Hours Available) - (Hours Required)

What's happening is that it is subtracting (Hours available) by row instead of subtracting from its total value.

Taking Month 1/Year 2000/China for example. The correct calculation should be:

((# of employees parameter. for this example = 50) * 160) - ((19164 * 1)+( 3291 * 0.5)+(3 * 2)) = 8000 - 20815 = -12815

• ###### 1. Re: Subtracting values from a total without resetting

Anyone? Cleaned up the worksheet abit.

• ###### 2. Re: Subtracting values from a total without resetting

Hi Alexander

Hope this helps.

You need to align the aggregation level, in this case I used LOD calc of "include".

** roundup/ roudndown creates little bit off value ?? 12817 vs 12815  ***

Thanks,

Shin

<< Ambassador Spotlight Here ! >>

• ###### 3. Re: Subtracting values from a total without resetting

Awesome this looks correct. Now how would you modify that formula to include all three "Employee Count" Parameters? The idea is that I the user can use a slider and see what happens when we add or subtract more employees for each country.

• ###### 4. Re: Subtracting values from a total without resetting

Hi Alexander

I am not sure what you exactly mean as to parameter control.

Could you clarify it little bit more in dtail?

Thanks,

Shin

• ###### 5. Re: Subtracting values from a total without resetting

I want to use the parameter sliders on the right as a variable input. Right now the formula you created uses parameter [EmployeeCountCHINA] which works with the corresponding slider. How would I incorporate the other two sliders in the formula so I could change Employee Count for each country individually?

• ###### 6. Re: Subtracting values from a total without resetting

Hi Alexander

I cannot verify with data becasue you don't have any count in Israel and Mexico.

But formula should be something like this.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 7. Re: Subtracting values from a total without resetting

This worked great thank you

• ###### 8. Re: Subtracting values from a total without resetting

Hi Alexander,

You are very welcome.

Thanks,

Shin