A cumulative totals by month on the X-axis with a fixed Y-axis

Hi, I have a dataset in Amazon Quicksight which shows customer purchase amounts, the date each transaction is booked, and the date when the transaction actually happens.

I want to create a table that shows the cumulative % of purchase value each month, and how long ago that purchase was booked.

In a single visual, I need to see how much time it takes for each month to accumulative 100% of it’s total purchase value. Ideally using the Pivot Table visual. I want to visualise this in a way that makes it easy to compare each month on a single row.

What I have so far is the cumulative value of the transactions and when they were booked. Here’s a screenshot:

Screenshot 2023-05-05 121939

But the problem is that the Y-axis makes it hard to compare each transaction month. So I want to turn the Y-axis (Booking Date) into a fixed range which says how long ago the transaction was booked for each month.

Here is a simple example of what I want it to look like:

|              | Jan-23 | Feb-22 | Nov-22 | Oct-22 |
| Same month   | 100%   | 100%   | 100%   | 100%   |
| Last month   | 90%    | 94%    | 92%    | 91%    |
| 2 months ago | 40%    | 50%    | 55%    | 30%    |

What can I do to make a pivot table (or other visual?) that shows a changing time on one axis, a fixed label on another axis, and a cumulative sum in each cell?

Right now I’m using a calculated field to create the cumulative % values, which looks like this:

   sum({Transaction Value}),
   [{Booking Date} ASC],
   [{Transaction Date}]
) / 
   sum({Transaction Value}),
   [{Transaction Date}]

And my dataset looks something like this (real data not included):

| Customer ID | Booking Date | Transaction Date | Transaction Value |
| ABC         | 1/03/2023    | 18/04/2023       | $10               |
| DEF         | 23/02/2023   | 13/04/2023       | $50               |
| EFG         | 16/02/2023   | 25/03/2023       | $75               |

Thank you!

Hmmm, on your y-axis can you make a calculated field that instead shows the datedifference from transaction date and booking date?

Then use this as your y-axis?

This should then for each row say 0->1->2 of the differences in month.

Does that make sense and is that what you’re looking for?

1 Like