Calculating the total of a column
trying to calculate the percentage of the total for each row in Beast mode. the formulas I have found don't work, because the SUM function only takes one single value per row instead of aggregating all of them. The percentage for each row is 100%. What formula should I use to get a total for all the values under a single column? I also tried ETL with no luck.
thanks,
`actual_hours`
/
(SUM(
Case when `active` = 'true'
THEN `actual_hours`
ELSE 0
END))
Best Answer
-
I believe you would need to do this within a data flow. You would want to add a column to the dataset that includes the total for given category.
For example:
Date Employee Group Actual Hours Total Group Hours 6/21/17 Jill Customer Service 6.5 43 6/21/17 Bernice Sales 7 45 6/21/17 Jeff Sales 7.25 45 You would need to create a branch in your Magic ETL that got the total hours for each group. Then you would join it back to your branch that has the actual hours for the employee based on the Group field. Then in the Card Builder you would create a BeastMode that looks like this:
=Actual Hours / Group Hours
KurtF
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2
Answers
-
I believe you would need to do this within a data flow. You would want to add a column to the dataset that includes the total for given category.
For example:
Date Employee Group Actual Hours Total Group Hours 6/21/17 Jill Customer Service 6.5 43 6/21/17 Bernice Sales 7 45 6/21/17 Jeff Sales 7.25 45 You would need to create a branch in your Magic ETL that got the total hours for each group. Then you would join it back to your branch that has the actual hours for the employee based on the Group field. Then in the Card Builder you would create a BeastMode that looks like this:
=Actual Hours / Group Hours
KurtF
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
please give the steps in mysql, especially join.
0 -
Hi
How do you create a column in ETL that sums an entire column?
I am trying to create a card that gives percentage of total, and from what I can see in this post, this will be the best way to do it.
But I can't figure out how to create a Total column.
Thanks
Simon
0 -
Since my first response, new functionality has been added to Magic ETL for this. You can use a Rank & Window tile. Check out the documentation at this link:
https://knowledge.domo.com/Prepare/Magic_Transforms/ETL_DataFlows/03ETL_Actions%3A_Rank_and_Window
At a high level, you will use the Rank & Window tile to create an overall total column. The output will be similar to what is represented above. The documentation should be sufficient to get you where you need to go.
KurtF
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Hi.
This solution works for the single visualisation of showing the `Actual Hours / Group Hours` by Employee and Group and Date.
How about if I want to use the same dataset to show `Actual Hours / Group Hours` by only Group and Date?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive