Previous month ending balance as current Month Beginning Balance

user00003
user00003 Member
in Magic ETL

Hi I am trying to create a beastmode that takes this months balance and subtract it from last months balance also known as this months beginning balance. 

 

sum(CASE WHEN `Month ` = '3' then `AR Balance` else 0 end) - sum(case when `Month ` = 4 then `AR Balance`else 0 end)

 

My problem with the above is the two values aren't ending up in the bar graph together under the current month. Instead I get a postive number for Month 3 and a negative number for Month 4 and I'm not sure how to make the appear in the same bar. 

 

Basically I need the change in the balance from last month to this month all as the value for this month. 

Best Answer

  • rado98
    rado98 Contributor
    Answer ✓

    Hi

    Using Magic ETL you could create a column with the balances offset by month.

     

    Filter in only the AR Balances

    Use the Rank and Window tile to create a column with the amounts offset by one month

     

    Use Beastmode to get the difference

     

    Apologies for not being able to be a detailed as I would want to.

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach

    Could you share a screenshot and a sample of your data set?  Also, it would be helpful if you showed an idea of what you are wanting as an output.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • user00003
    user00003 Member

    For example if I had the below data on the left I want the columns on the right. It has the AR beginning balance for February as the AR blanace that was in January. 

     

    DateAccount NameAmount AR Beginning balanceAR Difference 
    Jan-19Unbilled Sales105162   
    Jan-19AR Balance81963 AR Balance from December

    Dec Balance-81963

    Jan-19Deposit-6000   
    Feb-19Unbilled Sales185423   
    Feb-19AR Balance65974 8196381963 - 65974
    Feb-19Deposit-4125   
    Mar-19Unbilled Sales582133   
    Mar-19AR Balance49652 6597465974 - 49652
    Mar-19Deposit-7463   

     

    If I use the formula I would like to get the difference between these two numbers for February. This would show how the account changed within the month. 

     

    Febraury beginnning balance - Febraury Balance. = February AR Difference

     

    I think my main problem is getting the January balance to show as the February beginning balance and then subtracting the two so there is a knew number. 

     

    Or I could make a new row in the data on the left with an account name that says "AR Change". This would have the difference between February and January. 

     

    Also haven't been able to figure out how to pull the previous month number and subtract the current month number from that to get a new account. 

     

    DateAccount NameAmount
    Jan-19Unbilled Sales105162
    Jan-19AR Balance81963
    Jan-19Deposit on Contract-6000
    Jan-19AR DifferenceDEC balance -81963
    Feb-19Unbilled Sales185423
    Feb-19AR Balance65974
    Feb-19Deposit on Contract-4125
    Feb-19AR DIfference81963-65974
    Mar-19Unbilled Sales582133
    Mar-19AR Balance49652
    Mar-19Deposit on Contract-7463
    Mar-19AR Difference65974-49652

     

    Hope that makes sense ?

  • Sweep_The_Leg
    Sweep_The_Leg Member

    might need an extra case statement?

    case when  month = '2' then ((case when month = 2 then 'amount' else 0 end)-(case when month  = '1' then 'amount else 0 end)) else 0 end

     

    let me know ?

  • ST_-Superman-_
    ST_-Superman-_ Coach

    A case statement won't do the trick here.  The basic reason is that when evaluating a case statement, you can only look at one row at a time.  This means that you need your values for the previous month to be in the same row as your other values.  You can do this via an ETL data flow with a few transforms:3.png

     

    4.png

     

    5.png

     

    6.png

     

    7.png

     

     

    You can then create a flex table or regular table card to show the changes (or combo in a story)

    1.png

     

    2.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • ST_-Superman-_
    ST_-Superman-_ Coach

    Actually, the more I thought about it, if the flex table is sufficient, you can create it without the ETL at all:1.png

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • rado98
    rado98 Contributor
    Answer ✓

    Hi

    Using Magic ETL you could create a column with the balances offset by month.

     

    Filter in only the AR Balances

    Use the Rank and Window tile to create a column with the amounts offset by one month

     

    Use Beastmode to get the difference

     

    Apologies for not being able to be a detailed as I would want to.

  • user00003
    user00003 Member

    The flex table in Analyzer works but...the next part of this is to subtract out another account to get another value. 

     

    I tried the ETL but there must be way too much data because it ran for 20 hours before I canceled it.

     

    So I'm not sure where to go from here. 

     

    In the end I need (AR beginning balance - AR Ending balance) - Estimated Billings.

    If i can do this with the Flex table in Analyzer that would work but it doesn't appear that way.

     

    Thanks for all your help though it is really appreciated ?

  • ST_-Superman-_
    ST_-Superman-_ Coach

    You will need to do this through an ETL or MySQL data flow.

     

    If the ETL is taking that long, then there is probably something wrong with your settings.  

     

    If you have any Domo consulting hours, you may want to engage that team to take a look at your ETL and see if they can find the issue.  

     

    If you are comfortable posting the settings for your ETL here, then I could take a look as well.  

     

    Another option would be for you to post the schema for your data set (and maybe some sample data) in an excel sheet so that I could create the transforms in a MySQL dataflow which you could then just copy and paste the code into your instance.  

     

    I would need the full data set schema though, not just the few columns that were listed in the post.


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Categories