topic Calculation on a dataframe in Get Started Discussions https://community.databricks.com/t5/get-started-discussions/calculation-on-a-dataframe/m-p/46627#M1333 <P>Hi, I need to do following calculations on a dataframe. It should be done for each period and calculated value will be used for next period's calculation. Adding sample data and formula from excel here. Thanks in advance for your help.</P><P>Need to calculate&nbsp;NewCashFlow and&nbsp;NewAllocation for period 1 onwards.</P><P>Formula for NewCashFlow for period 1 is&nbsp;=(G5*F2)+E5+E2 and NewAllocation is&nbsp;=H5/SUM(H$5:H$7)</P><P>From Period 2 onwards NewCashFlow is&nbsp;=(G8*I5)+E8+H5 and NewAllocation is&nbsp;=H8/SUM(H$8:H$10)</P><P>Sample data is:</P><TABLE width="922"><TBODY><TR><TD width="64">Period</TD><TD width="84">HorizonDate</TD><TD width="142">GroupCode</TD><TD width="113">AssignmentCode</TD><TD width="93">CashFlowAmt</TD><TD width="64">AllocationPercent</TD><TD width="166">UnassignedCashFlowAmt</TD><TD width="96">NewCashFlow</TD><TD width="100">NewAllocation</TD></TR><TR><TD>0</TD><TD>3/31/2023</TD><TD>Group1</TD><TD>Allocation 1</TD><TD>38316684.90</TD><TD>0.47</TD><TD>0.00</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>0</TD><TD>3/31/2023</TD><TD>Group1</TD><TD>Allocation 2</TD><TD>2589294.65</TD><TD>0.03</TD><TD>0.00</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>0</TD><TD>3/31/2023</TD><TD>Group1</TD><TD>Allocation 3</TD><TD>41209290.55</TD><TD>0.50</TD><TD>0.00</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>1</TD><TD>4/30/2023</TD><TD>Group1</TD><TD>Allocation 1</TD><TD>-3087.23</TD><TD>0.00</TD><TD>5846.86</TD><TD>38316325.94</TD><TD>0.466620701</TD></TR><TR><TD>1</TD><TD>4/30/2023</TD><TD>Group1</TD><TD>Allocation 2</TD><TD>-208.62</TD><TD>0.00</TD><TD>5846.86</TD><TD>2589270.40</TD><TD>0.031532438</TD></TR><TR><TD>1</TD><TD>4/30/2023</TD><TD>Group1</TD><TD>Allocation 3</TD><TD>-3320.29</TD><TD>0.00</TD><TD>5846.86</TD><TD>41208904.49</TD><TD>0.501846861</TD></TR><TR><TD>2</TD><TD>5/31/2023</TD><TD>Group1</TD><TD>Allocation 1</TD><TD>-3104.73</TD><TD>0.00</TD><TD>-11987.00</TD><TD>38307627.82</TD><TD>0.466620701</TD></TR><TR><TD>2</TD><TD>5/31/2023</TD><TD>Group1</TD><TD>Allocation 2</TD><TD>-209.81</TD><TD>0.00</TD><TD>-11987.00</TD><TD>2588682.61</TD><TD>0.031532438</TD></TR><TR><TD>2</TD><TD>5/31/2023</TD><TD>Group1</TD><TD>Allocation 3</TD><TD>-3339.11</TD><TD>0.00</TD><TD>-11987.00</TD><TD>41199549.74</TD><TD>0.501846861</TD></TR><TR><TD>3</TD><TD>6/30/2023</TD><TD>Group1</TD><TD>Allocation 1</TD><TD>-3115.46</TD><TD>0.00</TD><TD>-2954.63</TD><TD>38303133.67</TD><TD>0.466620701</TD></TR><TR><TD>3</TD><TD>6/30/2023</TD><TD>Group1</TD><TD>Allocation 2</TD><TD>-210.53</TD><TD>0.00</TD><TD>-2954.63</TD><TD>2588378.91</TD><TD>0.031532438</TD></TR><TR><TD>3</TD><TD>6/30/2023</TD><TD>Group1</TD><TD>Allocation 3</TD><TD>-3350.66</TD><TD>0.00</TD><TD>-2954.63</TD><TD>41194716.31</TD><TD>0.501846861</TD></TR></TBODY></TABLE> Thu, 28 Sep 2023 16:21:08 GMT Hareesh1980 2023-09-28T16:21:08Z Calculation on a dataframe https://community.databricks.com/t5/get-started-discussions/calculation-on-a-dataframe/m-p/46627#M1333 <P>Hi, I need to do following calculations on a dataframe. It should be done for each period and calculated value will be used for next period's calculation. Adding sample data and formula from excel here. Thanks in advance for your help.</P><P>Need to calculate&nbsp;NewCashFlow and&nbsp;NewAllocation for period 1 onwards.</P><P>Formula for NewCashFlow for period 1 is&nbsp;=(G5*F2)+E5+E2 and NewAllocation is&nbsp;=H5/SUM(H$5:H$7)</P><P>From Period 2 onwards NewCashFlow is&nbsp;=(G8*I5)+E8+H5 and NewAllocation is&nbsp;=H8/SUM(H$8:H$10)</P><P>Sample data is:</P><TABLE width="922"><TBODY><TR><TD width="64">Period</TD><TD width="84">HorizonDate</TD><TD width="142">GroupCode</TD><TD width="113">AssignmentCode</TD><TD width="93">CashFlowAmt</TD><TD width="64">AllocationPercent</TD><TD width="166">UnassignedCashFlowAmt</TD><TD width="96">NewCashFlow</TD><TD width="100">NewAllocation</TD></TR><TR><TD>0</TD><TD>3/31/2023</TD><TD>Group1</TD><TD>Allocation 1</TD><TD>38316684.90</TD><TD>0.47</TD><TD>0.00</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>0</TD><TD>3/31/2023</TD><TD>Group1</TD><TD>Allocation 2</TD><TD>2589294.65</TD><TD>0.03</TD><TD>0.00</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>0</TD><TD>3/31/2023</TD><TD>Group1</TD><TD>Allocation 3</TD><TD>41209290.55</TD><TD>0.50</TD><TD>0.00</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>1</TD><TD>4/30/2023</TD><TD>Group1</TD><TD>Allocation 1</TD><TD>-3087.23</TD><TD>0.00</TD><TD>5846.86</TD><TD>38316325.94</TD><TD>0.466620701</TD></TR><TR><TD>1</TD><TD>4/30/2023</TD><TD>Group1</TD><TD>Allocation 2</TD><TD>-208.62</TD><TD>0.00</TD><TD>5846.86</TD><TD>2589270.40</TD><TD>0.031532438</TD></TR><TR><TD>1</TD><TD>4/30/2023</TD><TD>Group1</TD><TD>Allocation 3</TD><TD>-3320.29</TD><TD>0.00</TD><TD>5846.86</TD><TD>41208904.49</TD><TD>0.501846861</TD></TR><TR><TD>2</TD><TD>5/31/2023</TD><TD>Group1</TD><TD>Allocation 1</TD><TD>-3104.73</TD><TD>0.00</TD><TD>-11987.00</TD><TD>38307627.82</TD><TD>0.466620701</TD></TR><TR><TD>2</TD><TD>5/31/2023</TD><TD>Group1</TD><TD>Allocation 2</TD><TD>-209.81</TD><TD>0.00</TD><TD>-11987.00</TD><TD>2588682.61</TD><TD>0.031532438</TD></TR><TR><TD>2</TD><TD>5/31/2023</TD><TD>Group1</TD><TD>Allocation 3</TD><TD>-3339.11</TD><TD>0.00</TD><TD>-11987.00</TD><TD>41199549.74</TD><TD>0.501846861</TD></TR><TR><TD>3</TD><TD>6/30/2023</TD><TD>Group1</TD><TD>Allocation 1</TD><TD>-3115.46</TD><TD>0.00</TD><TD>-2954.63</TD><TD>38303133.67</TD><TD>0.466620701</TD></TR><TR><TD>3</TD><TD>6/30/2023</TD><TD>Group1</TD><TD>Allocation 2</TD><TD>-210.53</TD><TD>0.00</TD><TD>-2954.63</TD><TD>2588378.91</TD><TD>0.031532438</TD></TR><TR><TD>3</TD><TD>6/30/2023</TD><TD>Group1</TD><TD>Allocation 3</TD><TD>-3350.66</TD><TD>0.00</TD><TD>-2954.63</TD><TD>41194716.31</TD><TD>0.501846861</TD></TR></TBODY></TABLE> Thu, 28 Sep 2023 16:21:08 GMT https://community.databricks.com/t5/get-started-discussions/calculation-on-a-dataframe/m-p/46627#M1333 Hareesh1980 2023-09-28T16:21:08Z