How to use a value in previous line of table

Shevy

I know this could be done in the SQL code and brought in with my table, but hoping it can be dne in beast mode as hat would allow for greater flexibility. 

 

I have a table - (sample below) - Assume it is April 15th - end of the day (50%) of the way thoguh month.

 

DateOne, Plan and Actual all are pulled in from an SQL query.  Forecast1 is a Beast Mode Calculation that looks at the days of the current paste and adjusts the actual to a straightline based on number of days in month.  In the example below we are 50% of the way through the month so the number doubles. 

 

Forecast2 is the one I cannot figure out.  I would like to take either the Actual from the current month or Forcast1 from current monthas a starting point to do a clcultion in later months.  In the table below, for example, it takes the April Actual of 15 and multiplies by 1.  I can get the Beast mode to identify the month correctly and i can hard code a 15 into the outer months, but i cannot figure out how to pull the 15 from April actual to use it in other row calculations.  In short, i can do the whole table below other than than the numbers in RED based on a calculation.  Can it be done in Beast Mode?

 

DateOne	Plan	Actual	Forecast1	Forecast2
1/1/2015	20	15
2/1/2015	20	18
3/1/2015	20	25
4/1/2015	22	15	30
5/1/2015	22			15
6/1/2015	22			15
7/1/2015	25			15
8/1/2015	25			15
9/1/2015	25			15
10/1/2015	25			15
11/1/2015	27			15
12/1/2015	27			15

jmo

@Shevy,

 

Good question. The short answer is that you cannot do this calculation with Beast Mode.

 

Think of the card builder as a point and click SQL query designer. The SQL query you are designing does not allow for joins or subqueries and in many cases has an implied GROUP BY statement (depending on the rest of the settings in your card).

 

In this case I'm pretty sure you need a subquery to get the actual value from April (15 in this example) so you can pull it into the other rows.

Mawdo81

So it can't be done in Beat Mode, but it can be done as part of a Magic Transformation?  What would be the most performant way of doing that across multiple categories and c80m rows please?