SubTotal Category Remaining Amount: How to calculate difference between two columns
How to display the value of "Remaining Amount' in subtotal category:
I have the following scenario, using a Pivot table Displaying the budget amount and actual amount along with remaining amount.
for every subcategory I do not want to display remaining amount if either value budget or Actual value is 0.
However I will still like to display a value for remaining amount if either the Budget or Actual value exist (My understanding is that I the value rolls up and adds to display subtotal values).
Below is the formula's I used to calculate Remaining Amount in BeastMode calculation.
sum(case when `Transaction_Type` = 1 then `Amount` else 0 end) =0
sum(case when `Transaction_Type` = 0 then `Amount` else 0 end) =0
sum(case when `Transaction_Type` = 1 then `Amount` else 0 end)
sum(case when `Transaction_Type` = 0 then `Amount` else 0 end)
Instead of 0 in your code can you put '' in the beast mode. So if budget or actual value is 0 then '' .
Does that give you what you need?
Because the way pivot tables behave and the fact that subtotals are an all or nothing setting what you're looking to do isn't possible at this time.**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
@rahul93 is partially correct, if you put a '' in your ELSE clause you will convert your beastmode into a string. It therefore wouldn't format or behave like a number after that point. probably not desireable.
To get 'nothing' as Rahul is recommending, you can however use ELSE NULL
sum(case when `Transaction_Type` = 1 then `Amount` else null end) -- or sum(case when `Transaction_Type` = 1 then `Amount` end) =0
Now that said you have to be careful with null
if you take
5 - NULL
the result witll be NULL
@GrantSmith talks about this at the IDEAS Exchange Conference, https://www.youtube.com/watch?v=gO8OLpsAk4M&list=PLUy_qbtzH0S6-5oDbx3BsIv2Xk-JxJxWi&index=6
test if your amounts net to 0 if so replace with null
case when sum(case when ... then amount else 0 end) - sum(case when ... then budget else 0 end) = 0 then null else sum(case when ... then amount else 0 end) - sum(case when ... then budget else 0 end) end
THAT SAID. WHAT YOU'RE ASKING FOR IS LOGICALLY INAPPROPRIATE
Null and 0 are not the same thing.
If i had 5 dollars and spent 5 dollars i have zero dollars left.
That's different from I spent nothing and i had no budget therefore the value should be null.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Hi, using a '' doesn't convert the field into a string field. I just tested it and it works as a numeric field. See screenshots
- 7.6K All Categories
- 917 Connectors
- 242 Workbench
- 472 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 445 Datasets
- 31 Visualize
- 197 Beast Mode
- 2K Charting
- 7 Variables
- 14 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 14 Manage
- 35 Governance & Security
- 19 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 15 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部