How to form aggregate-of-aggregate values that can be filtered by card date ranges?

alnprk

I am trying to create a table in Visualizer that displays how many customers have made at least 1, 2, etc. purchases as below. The original dataset is raw transaction data, and I've tried creating columns that display the "total # of purchases" by the customer for each transaction (aka each row in the data).

This does work, but the problem is this "total # of purchases" column becomes inaccurate when the user changes the date range of the card (i.e. someone that made 1 transaction in July and 3 in August will have a "total # of purchases" correctly displayed as 4, but if the date range is narrowed to just August the value should change to 3). As user changes to the card date range cannot affect ETL/Viewer date ranges, this approach does not work.

As Visualizer's Beast Mode does not accept aggregates of aggregates, I'm having a hard time implementing the columns there. Can anyone suggest any approaches I can take in Visualizer?

ArborRose

I have a meeting to get to in a couple minutes. So this is going to be a bit rushed. Hopefully this helps…

Creating a sample set:

Customer Name	Purchase Date	Transaction Amount
John Doe	2024-07-01	50.00
Jane Smith	2024-07-02	75.00
John Doe	2024-08-01	30.00
Jane Smith	2024-08-03	40.00
John Doe	2024-09-01	100.00
Mike Johnson	2024-08-01	20.00
John Doe	2024-09-15	25.00
Mike Johnson	2024-09-15	60.00
Jane Smith	2024-09-20	35.00
Mike Johnson	2024-09-21	15.00
Sarah Lee	2024-09-01	100.00

distinct:

COUNT(DISTINCT `Customer Name`)

grouping:

CASE

    WHEN COUNT(`Purchase Date`) OVER (PARTITION BY `Customer Name`) >= 10 THEN '10+ transactions'

    WHEN COUNT(`Purchase Date`) OVER (PARTITION BY `Customer Name`) = 9 THEN '9 transactions'

    WHEN COUNT(`Purchase Date`) OVER (PARTITION BY `Customer Name`) = 8 THEN '8 transactions'

    WHEN COUNT(`Purchase Date`) OVER (PARTITION BY `Customer Name`) = 7 THEN '7 transactions'

    WHEN COUNT(`Purchase Date`) OVER (PARTITION BY `Customer Name`) = 6 THEN '6 transactions'

    WHEN COUNT(`Purchase Date`) OVER (PARTITION BY `Customer Name`) = 5 THEN '5 transactions'

    WHEN COUNT(`Purchase Date`) OVER (PARTITION BY `Customer Name`) = 4 THEN '4 transactions'

    WHEN COUNT(`Purchase Date`) OVER (PARTITION BY `Customer Name`) = 3 THEN '3 transactions'

    WHEN COUNT(`Purchase Date`) OVER (PARTITION BY `Customer Name`) = 2 THEN '2 transactions'

    ELSE '1 transaction'

END

test:

COUNT(`Purchase Date`) OVER (PARTITION BY `Customer Name`)

alnprk

Though not exactly it, thanks for responding! I was looking for something closer to the table below, where the rows in the table are based on how many customers made at least 1, 2, 3, etc. transactions.

The problem I'm now having is that each row should also be including the counts of the rows above (as a customer can't make 3 transactions without making 2 and 1 first). Is there any way to make a Visualizer table's rows not mutually exclusive?