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
Red 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
Red 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!

Announcements
Win free lodging at Domoapalooza! We want you to share you favorite Domo tips and tricks. Click here for more details!