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

0

Hello,

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

branchskudateqty_shippedqty_receivedqty_adjustedprice
branch1sku18/1/2301010.00
branch1sku17/1/2350015.00
branch1sku16/1/2300720.00

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

branchskuon_handlatest_priceinventory_value
branch1sku1215.0030.00

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

branchskuon_handlatest_priceinventory_value
branch1sku1720.00140.00

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!

lg2279
asked a month ago267 views
1 Answer
0

Hello,

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 !!

AWS
SUPPORT ENGINEER
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