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!