Trying to calculate percentages for a group values
I am trying to calculate a percentage for each "Bucket".
Less than 0 days past due
1 to 15 days past due
16 to 30 days past due
31 to 60 days past due
I have each calculation and the formula works for each "Bucket" but I cant get them all in one beast mode calculation. Here are my formulas:
Sum(Case
WHEN `DaysPastDue`<= 0 THEN `TotalDue` else 0 end)/sum(`TotalDue`)
Sum(CASE
WHEN `DaysPastDue`>= 1 AND `DaysPastDue`<= 15 THEN `TotalDue` else 0 end)/sum(`TotalDue`)
Sum(Case
WHEN `DaysPastDue`>= 16 AND `DaysPastDue`<= 30 THEN `TotalDue` else 0 end)/sum(`TotalDue`)
sum(case
WHEN `DaysPastDue`>= 31 AND `DaysPastDue`<= 60 THEN `TotalDue` else 0 end)/sum(`TotalDue`)
Sum(case
WHEN `DaysPastDue`>= 61 THEN `TotalDue`else 0 end)/ sum(`TotalDue`)
How do I get them all under one calculation?
Please advise.
Best Answer
-
I'm going to assume this for a table card in my explanation, but you should be able to modify this fairly easily.
Also, youre `TotalDue` field, I'm assuming this needs to be a constant value of all of the item due. If it's not you'll need to calculate that ahead of time and then have it repeat through your dataset, let's call the new column AllDue. This will allow you to return MAX(`AllDue`) at any point in the following calculations and reference all items due (not just a subset, which is what will happen going forward).
So the first part is to identify the "Buckets". This could be the first column of the table:
CASE WHEN `DaysPastDue` <= 0 THEN 'Less than 0 days past due'
WHEN `DaysPastDue` >= 1 AND `DaysPastDue` <= 15 THEN '1 to 15 days past due'
WHEN `DaysPastDue` >= 16 AND `DaysPastDue` <= 30 THEN '16 to 30 days past due'
WHEN `DaysPastDue` >= 31 AND `DaysPastDue` <= 60 THEN '31 to 60 days past due'
ENDYou can then follow the same pattern when creating your percentage calculation:
SUM(CASE WHEN `DaysPastDue` <= 0 THEN `TotalDue`
WHEN `DaysPastDue` >= 1 AND `DaysPastDue` <= 15 THEN `TotalDue`
WHEN `DaysPastDue` >= 16 AND `DaysPastDue` <= 30 THEN `TotalDue`
WHEN `DaysPastDue` >= 31 AND `DaysPastDue` <= 60 THEN `TotalDue`
END)
/
MAX(`AllDue`)That paired with the buckets beastmode will give you your values and series that you can use.
Let me know if you have any questions on this,
Valiant
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.1
Answers
-
I'm going to assume this for a table card in my explanation, but you should be able to modify this fairly easily.
Also, youre `TotalDue` field, I'm assuming this needs to be a constant value of all of the item due. If it's not you'll need to calculate that ahead of time and then have it repeat through your dataset, let's call the new column AllDue. This will allow you to return MAX(`AllDue`) at any point in the following calculations and reference all items due (not just a subset, which is what will happen going forward).
So the first part is to identify the "Buckets". This could be the first column of the table:
CASE WHEN `DaysPastDue` <= 0 THEN 'Less than 0 days past due'
WHEN `DaysPastDue` >= 1 AND `DaysPastDue` <= 15 THEN '1 to 15 days past due'
WHEN `DaysPastDue` >= 16 AND `DaysPastDue` <= 30 THEN '16 to 30 days past due'
WHEN `DaysPastDue` >= 31 AND `DaysPastDue` <= 60 THEN '31 to 60 days past due'
ENDYou can then follow the same pattern when creating your percentage calculation:
SUM(CASE WHEN `DaysPastDue` <= 0 THEN `TotalDue`
WHEN `DaysPastDue` >= 1 AND `DaysPastDue` <= 15 THEN `TotalDue`
WHEN `DaysPastDue` >= 16 AND `DaysPastDue` <= 30 THEN `TotalDue`
WHEN `DaysPastDue` >= 31 AND `DaysPastDue` <= 60 THEN `TotalDue`
END)
/
MAX(`AllDue`)That paired with the buckets beastmode will give you your values and series that you can use.
Let me know if you have any questions on this,
Valiant
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 600 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 685 Beast Mode
- 43 App Studio
- 38 Variables
- 655 Automate
- 170 Apps
- 438 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 383 Distribute
- 110 Domo Everywhere
- 267 Scheduled Reports
- 6 Software Integrations
- 111 Manage
- 108 Governance & Security
- 8 Domo University
- 25 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive