amazon quicksights compare time periods

8.1k views Asked by At

Can line charts or bar charts compare two time periods?
Example compare sales revenue between 2017 to 2016 by month, to see how Jan 2017 compares to Jan 2016, Feb 17 to Feb 16, etc.

In the chart I'd like to see whether I'm up or down between the periods. The data can be manipulated anyway required, but simplistically looks like this

currentDate CurrentSum  PreviousSum
1/1/2016    1949    1917
2/1/2016    2217    1949
3/1/2016    2180    2217
4/1/2016    2197    2180
5/1/2016    2320    2197
6/1/2016    2267    2320
7/1/2016    2187    2267
8/1/2016    2355    2187
9/1/2016    2226    2355
10/1/2016   2175    2226
11/1/2016   2356    2175
12/1/2016   1939    2356
1/1/2017    2072    1939
2/1/2017    2310    2072
3/1/2017    2453    2310
4/1/2017    2128    2453
5/1/2017    2515    2128
6/1/2017    2403    2515
7/1/2017    1244    2403
1

There are 1 answers

2
Tobi On

I managed to do this using line charts. In my case, I visualize a running sum of sales over each month and filter the result to only show the past 3 months:

enter image description here

You should be able to achieve the same thing by modifying my solution a bit:

  1. Create two calculated fields with the following formulas: saleDateMonth = extract('MM',saleDate) and saleDateYear = extract('YY',saleDate)
  2. Create a calculated field representing the running sum over each month with the following formula: runningSumSales = runningSum(sum({sale price}),[{saleDateMonth} ASC],[{saleDateYear}])
  3. Create a line chart/visual
  4. Use the calculated fields for the field wells as follows: x-axis: saleDateMonth, value = runningSumSales and color: saleDateYear. Note that these three fields correspond to the fields used in the running sum formula.

My field wells look as follows:

enter image description here

Note that you probably have to adapt your data fields names to the solution above but I think that should be quite easy. Also to get only the past 3 months I use a standard filter with a relative date range.