Create a tier label based on the aggregate value of the client instead of the account level

Reply
Highlighted
White Belt

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

 

 


Accepted Solutions
Highlighted
Black Belt

Re: Create a tier label based on the aggregate value of the client instead of the account level

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, Client

You 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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Highlighted
Black Belt

Re: Create a tier label based on the aggregate value of the client instead of the account level

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, Client

You 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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Highlighted
White Belt

Re: Create a tier label based on the aggregate value of the client instead of the account level

Thank you so much!

Highlighted
White Belt

Re: Create a tier label based on the aggregate value of the client instead of the account level

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"

 

Highlighted
Red Belt

Re: Create a tier label based on the aggregate value of the client instead of the account level

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'
END

 

To 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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
White Belt

Re: Create a tier label based on the aggregate value of the client instead of the account level

Thank you 

 

 
 
Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.