5 Replies Latest reply on Jul 24, 2018 5:50 PM by Zhouyi Zhang

# Calculating running sum along with Linearity

Hello community,

This is my first time posting on this site. So here's my situation:

So I'm trying to do a calculation for the linearity in percentage. The formula is:

Running Sum Sales / (Target Number)

The target number is a manual number and different for each quarter. I'm trying to do a calculation that will do the running sum throughout the year, restart at the beginning of a new quarter and incorporate the new target number. Here's what I have so far. I'm using 1000 Start Ups as the data set with the Order Date and sales fields. I'm only using one year. Please help with the calculation or a better method:

• ###### 1. Re: Calculating running sum along with Linearity

Hi, Shelnes

Is it something like this?

Hope this helps

ZZ

• ###### 2. Re: Calculating running sum along with Linearity

Hi Shelnes,

your use of If case is what is causing the issue.

In cases where you must use multiple conditionals, you can either use if ... elseif clauses or Case Statements.

You are doing good with the table calculation in terms of what you want, so here is my solution in case statements

Hope this helps.

• ###### 3. Re: Calculating running sum along with Linearity

Hi Zhouyi. This works perfectly. One more thing. Lets say I want to create this calculated field but also include Divisions in it also. How would I go about it. For example:

If Division = EAST

If Min(DATEPART('quarter',[FC Live Date])) = 1 THEN

RUNNING_SUM(SUM([Install Revn]))/(44100*10)

ELSEIF  Min(DATEPART('quarter',[FC Live Date])) = 2 THEN

RUNNING_SUM(SUM([Install Revn]))/(44100*10)

ELSEIF  Min(DATEPART('quarter',[FC Live Date])) = 3 THEN

RUNNING_SUM(SUM([Install Revn]))/(44100*10)

ELSEIF  Min(DATEPART('quarter',[FC Live Date])) = 4 THEN

RUNNING_SUM(SUM([Install Revn]))/(48600*10)

If Division = WEST

If Min(DATEPART('quarter',[FC Live Date])) = 1 THEN

RUNNING_SUM(SUM([Install Revn]))/(43700*10)

ELSEIF  Min(DATEPART('quarter',[FC Live Date])) = 2 THEN

RUNNING_SUM(SUM([Install Revn]))/(43900*10)

ELSEIF  Min(DATEPART('quarter',[FC Live Date])) = 3 THEN

RUNNING_SUM(SUM([Install Revn]))/(44000*10)

ELSEIF  Min(DATEPART('quarter',[FC Live Date])) = 4 THEN

RUNNING_SUM(SUM([Install Revn]))/(48400*10)

END

• ###### 4. Re: Calculating running sum along with Linearity

Hi Jeevan, this works perfect. Would I be able to include an East and West Division to this calculation also? This way I don't have to include the East and West filters. For Example:

If Division = East Then

RUNNING_SUM(SUM([Sales]))/(Target)

If Division = West Then

RUNNING_SUM(SUM([Sales]))/(Target)

END

• ###### 5. Re: Calculating running sum along with Linearity

Hi, Shelnes