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

user04775
user04775 Member
in Magic ETL

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

  • MarkSnodgrass
    MarkSnodgrass Coach
    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, 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    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, 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • user04775
    user04775 Member

    Thank you so much!

  • user04775
    user04775 Member

    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"

     

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user04775
    user04775 Member

    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

     
     
     

Categories