Create a tier label based on the aggregate value of the client instead of the account level
Hi.
Wondering if somebody can help me with the creation of Tier Labels based on the aggregate values at client level and not the individual account.
My data is organized as follows
Reporting month
Account
Product Type
ClientNo.
Balance
This is what I was doing but I got stuck with the creation of the labels and the filtering of the products I need to report at client level
SELECT Reporting Month, Client , sum(balance) as Clientbalance
FROM TABLE NAME
WHERE Product Type IN ('DDA','NOW','MMK','OVN','TDS')
GROUP by "Client"
The lables I need would depend on the ClientBalance from Above and they would be 3 :
<100M
100M - 200M
>200M
Best Answer
-
Assuming you want to group by month and client, I would suggest adding a CASE statement to build the labels. It would look like this:
SELECT Reporting Month, Client , sum(balance) as Clientbalance,
CASE WHEN SUM(balance) < 10000000 THEN 'Less than 100m'
WHEN SUM(balance) < 20000000 THEN '100m - 200m'
ELSE 'Greater than 200m'
END AS BalanceLabel
FROM TABLE NAME
WHERE Product Type IN ('DDA','NOW','MMK','OVN','TDS')
GROUP BY Reporting Month, ClientYou could also do this statement as a beast mode field if you didn't want to add it to your SQL statement.
Hope this helps.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.3
Answers
-
Assuming you want to group by month and client, I would suggest adding a CASE statement to build the labels. It would look like this:
SELECT Reporting Month, Client , sum(balance) as Clientbalance,
CASE WHEN SUM(balance) < 10000000 THEN 'Less than 100m'
WHEN SUM(balance) < 20000000 THEN '100m - 200m'
ELSE 'Greater than 200m'
END AS BalanceLabel
FROM TABLE NAME
WHERE Product Type IN ('DDA','NOW','MMK','OVN','TDS')
GROUP BY Reporting Month, ClientYou could also do this statement as a beast mode field if you didn't want to add it to your SQL statement.
Hope this helps.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.3 -
Thank you so much!
0 -
Hi,
I am not that familiar with sql, but having said that and using this post I was able to create a tier label based on the clients liabilities avg balances (See below). The problem I have now is that I ended up with a subset of my original dataflow. Can I just add this new column to the original dataset to be able to report on other information the original dataflow has.
SELECT "REPORTING MONTH", "CustomerNo" , sum("MonthAverageUSD") as ClientAvgbalance,
CASE WHEN SUM("MonthAverageUSD") < 100000 THEN 'TIER V'
WHEN SUM("MonthAverageUSD") < 250000 THEN 'TIER IV'
WHEN SUM("MonthAverageUSD") < 500000 THEN 'TIER III'
WHEN SUM("MonthAverageUSD") < 1000000 THEN 'TIER II'
ELSE 'TIER I'
END AS BalanceLabel
FROM "chile_bancas_new"
WHERE "Type" IN ('DDA','NOW','MMK','OVN','TDS')
GROUP BY "REPORTING MONTH", "CustomerNo"0 -
Hey @user04775 ,
Instead of hard coding this into a dataset, consider building a beastmode that creates these Balance Labels and then visualize this data in a card as @MarkSnodgrass recommended.
The beast mode would just be the CASE statement.
CASE WHEN SUM("MonthAverageUSD") < 100000 THEN 'TIER V'
WHEN SUM("MonthAverageUSD") < 250000 THEN 'TIER IV'
WHEN SUM("MonthAverageUSD") < 500000 THEN 'TIER III'
WHEN SUM("MonthAverageUSD") < 1000000 THEN 'TIER II'
ELSE 'TIER I'
ENDTo make sure you only consider the correct types. Implement them as filters in your card.
SIDE NOTE:I'd think twice about taking the SUM of an AVERAGE, unless you're confident that that's what you actually want. Think through if those numbers make sense for your business requirements, and also ask if you might not get higher quality reporting if you avoid the average, or move it to a later stage in your data processing.
In most financial reporting use cases I've worked with, I've been told to take the LAST reported balance per month or the avg balance between the start and end of month, but not avg. of daily balance... but again, every company is different.
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 -
Thank you jaeW_at_Onyx,
The only problem I have with adding the tier label as a beast mode is that the label is based on only the liabilities of the client and not all the products the client might have, and only works if I have the clients in card. It gives me an error if i need to do summaries by Business units and tiers, or officers and tiers. I hear your comment to about using averages but in our case we use that column to avoid seeing the same client jumping from one tier to another every month.
Thanks,
Carla
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