How to use a value in previous line of table

Shevy
Shevy Contributor
in Archive

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?

 

DateOnePlanActualForecast1Forecast2
1/1/20152015  
2/1/20152018  
3/1/20152025  
4/1/2015221530 
5/1/201522  15
6/1/201522  15
7/1/201525  15
8/1/201525  15
9/1/201525  15
10/1/201525  15
11/1/201527  15
12/1/201527  

15

 

Dojo Community Member
** Please like responses by clicking on the thumbs up
** Please Accept / check the answer that solved your problem / answered your question.

Comments

  • jmo
    jmo Domo Employee

    @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.

    -----
    I work for Domo.
    **Say "Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as "Accepted Solution"
  • Mawdo81
    Mawdo81 Member

    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?

Categories