Grouping by client_id without losing row data

user060355
user060355 Member
in Charting

I have a table of client purchases with columns:

  • sale date
  • client ID
  • sale total

I'd like to group by client ID so that I end up with total sales for each client however when I do that in the data flow, I lose all date information for the sale.

When I use a date formula to pull out sale year and include that in my grouping, I'm able to separate out each client's sales by year, but the data is rigid and I'm unable to group by month in my cards without modifying the data flow and adding a new output.

Is grouping by client ID possible in a beast mode? When I try to use a case statement I need to say case when client_id = X however I'd like that X to continue to change for ALL client IDs. As a result I'd need a new case statement for every client_id which seems crazy.

What am I missing?

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    if you want to calculate livetime value.

    In ETL you can GROUP BY Client ID and sum(Amount) then JOIN that back onto the transactions.

    OR you can use a RANK & WINDOW function to calculate cumulative Sum (so you know lifetime value at the time the transaction took place).


    Or do the same thing in DataSet Views using window functions and avoid ETL altogether.

    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"

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    You shouldn't need to do any ETL or beast mode work for this. If using a table card, for example, drag in all 3 columns into your column listing. Click the pencil next to sales total and choose aggregation of Sum. In the date range filter, choose group by year. This should give you the sales totals for each client for each year.

    **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.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    what question are you trying to answer in your card?

    there's no reason to group your data in ETL if in analyzer all you want to do is show sales grouped by client, just toss the client on the X-axis and sum(sale total) on the y-axis.

    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"
  • user060355
    user060355 Member

    My apologies. I thought I had provided sufficient information in my question, but I did not :/ . I am trying to calculate customer lifetime value and there are additional columns I want to be able to group on as well such as the office the client visits.

    I'm not looking to actually include the client's ID in the final card. If I use a pivot chart card and include sale year as my rows and office as my columns, it displays the office's total sales and not the avg. client's total sales.

    Does that make any sense?

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    if you want to calculate livetime value.

    In ETL you can GROUP BY Client ID and sum(Amount) then JOIN that back onto the transactions.

    OR you can use a RANK & WINDOW function to calculate cumulative Sum (so you know lifetime value at the time the transaction took place).


    Or do the same thing in DataSet Views using window functions and avoid ETL altogether.

    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"

Categories