QuickSight: Aggregating columns of dataset along with point-in-time values from same dataset



I have a MySQL custom query sourced dataset that includes a list of transactions with the following fields:

  • branch - The branch the transaction occurred at
  • sku - The item the transaction was for
  • date - The date the transaction occurred
  • qty_shipped - The amount of the item that was shipped as part of the transaction
  • qty_received - The amount of the item that was received as part of the transaction
  • qty_adjusted - The amount of the item that was adjusted as part of the transaction
  • price - The price of the sku at the time of the transaction

I'd like to create an analysis visual (I think in this case it'd be a pivot table) that has a date filter "as_of_date" and contains the following rows:

  • branch
  • sku
  • on_hand - The sum of the following columns aggregated: qty_received - qty_shipped + qty_adjusted as of the as_of_date filter, i.e. SUM(qty_received - qty_shipped + qty_adjusted)
  • latest_price - The price of the sku as of the as_of_date filter. This value may be null for a particular transaction so we want to use the latest valid value or null/0 if none exists
  • inventory_value - This is a simple calculation of the on_hand * latest_price

For example, given the following rows in the dataset: Note: Date format is MM/DD/YY


with the as_of_date filter set to 7/15/23 I'd expect the following output:


if we update the filter to 6/15/23 I'd expect the following output:


Put plainly, this is an inventory valuation report that will be used to view the value of inventory at a branch at various points in time. I've tried various ways to get this set up but I'm unable to get the correct point-in-time price value for the sku. The summing of the 'qty' fields seems to work correctly though. I'm also open to updating the query for the dataset but I believe this should be doable with the current dataset.

Any help would be greatly appreciated, TIA!

asked a month ago267 views
1 Answer


I would like to inform you that Quicksight support multiple visual types and various aggregation modes for data to be visualized in Tables. However, with your explanation, I believe you are trying to retrieve the cumulative data for each month from datasource using a SQL and then visualizing in Analysis as a plain table with "as_of_date" filter. Please correct me if my understanding varies from your setup.

In the sample data you had provided, I see that there is no "as_of_date" column, hence I assume you are referring the "as_of_date" filter to the "date" dataset. In the above data, I see the fields having values as below,

date	qty_shipped	qty_received	qty_adjusted
8/1/23	0		1		0
7/1/23	5		0		0
6/1/23	0		0		7

And, the calculation for "on_hand" field is as below,

SUM(qty_received - qty_shipped + qty_adjusted)

With above data, in case you have "as_of_date" filter having definition as below, it should give the results as you expected.

Condition : Before
Use parameters : true
End date parameter : <<date picker parameter attached to a control>>

Thus, I request you to please verify the Filter definition if it matches above description. Further, in case you continue to observe the issue even after having filter definition as above, I recommend you to kindly consider raising a case to AWS Support team so that an engineer from Quicksight support team can investigate by understanding your setup, usecase and provide you possible mitigation measures.

Thank you !!

answered a month ago
  • Hi Mounika,

    Thank you for the response! The as_of_date filter is just a filter on the "date" field from the dataset that filters out records that have a "date" value after the filter value. So the summed "qty_*" fields should only be from rows that have a "date" before the as_of_date filter value and the "latest_price" column should be populated by the value for "price" from the row with a "date" value closest to or equal to the as_of_date filter value i.e. the most recent price as of the given date filter value.

    Thanks again!

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions