# 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:

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:

``````runningSum(
sum({Transaction Value}),
[{Booking Date} ASC],
[{Transaction Date}]
) /
sumOver(
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