Approach to Comparing Two Date Ranges of Data in a KPI Visualization

Question & Answer
1

All,

I’m trying to replicate functionality available in a competitive tool in QuickSight. The challenge is as follows:

Assume a single data source with 2 columns, a Date Column and a Quantity Column.

The ask is to create an analysis that compares the sum of Quantity for one range of dates to the sum of Quantity for a different range of dates in a KPI visualization.

For example, a comparison of total quantity for January 1, 2021 thru January 15, 2021 to the total quantity for Feb 3, 2021 thru Feb 17, 2021…with the ability to set the date ranges through date controls.

Have tried creating an analysis with two data sets but I can’t seem to populate the visualization with data from two separate data sets.

Thoughts on an approach?

Thanks!

2

You need to create 4 parameters (and controls) as follows:

  1. ${start1} will contain the start date of the first period
  2. ${end1} will contain the end date of the first period
  3. ${start2} will contain the start date of the second period
  4. ${end2} will contain the end date of the second period

Then, set up two calculated fields as follows:

{Total Quantity Period 1}

sum(ifelse({Date} >= ${start1} AND {Date} <= ${end1}, {Quantity}, 0))

and

{Total Quantity Period 2}

sum(ifelse({Date} >= ${start2} AND {Date} <= ${end2}, {Quantity}, 0))

Finally, use the two calculated fields you just created in a KPI visual.

3

Darcoli - Thanks! Just tested and works as advertised.

1 Like