Previous month ending balance as current Month Beginning Balance
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
-
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.
0
Answers
-
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.” -Superman0 -
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.
Date Account Name Amount AR Beginning balance AR Difference Jan-19 Unbilled Sales 105162 Jan-19 AR Balance 81963 AR Balance from December Dec Balance-81963
Jan-19 Deposit -6000 Feb-19 Unbilled Sales 185423 Feb-19 AR Balance 65974 81963 81963 - 65974 Feb-19 Deposit -4125 Mar-19 Unbilled Sales 582133 Mar-19 AR Balance 49652 65974 65974 - 49652 Mar-19 Deposit -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.
Date Account Name Amount Jan-19 Unbilled Sales 105162 Jan-19 AR Balance 81963 Jan-19 Deposit on Contract -6000 Jan-19 AR Difference DEC balance -81963 Feb-19 Unbilled Sales 185423 Feb-19 AR Balance 65974 Feb-19 Deposit on Contract -4125 Feb-19 AR DIfference 81963-65974 Mar-19 Unbilled Sales 582133 Mar-19 AR Balance 49652 Mar-19 Deposit on Contract -7463 Mar-19 AR Difference 65974-49652 Hope that makes sense ?
0 -
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 ?
0 -
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:
You can then create a flex table or regular table card to show the changes (or combo in a story)
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
Actually, the more I thought about it, if the flex table is sufficient, you can create it without the ETL at all:
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
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.
0 -
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 ?
0 -
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.” -Superman0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive