Grouping by client_id without losing row data

user060355 · 2021-10-27T15:56:10+00:00

There was an error rendering this rich post.

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?

Find more posts tagged with

Accepted answers

jaeW_at_Onyx

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.

All comments

MarkSnodgrass

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.

jaeW_at_Onyx

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.

user060355

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

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.