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

Question

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
| branch | sku | date | qty_shipped | qty_received | qty_adjusted | price |
| --- | --- | --- | --- | --- | --- | --- |
| branch1 | sku1 | 8/1/23 | 0 | 1 | 0 | 10.00 |
| branch1 | sku1 | 7/1/23 | 5 | 0 | 0 | 15.00 |
| branch1 | sku1 | 6/1/23 | 0 | 0 | 7 | 20.00 |

with the as_of_date filter set to 7/15/23 I'd expect the following output:
| branch | sku | on_hand | latest_price | inventory_value |
| --- | --- | --- | --- | --- |
| branch1 | sku1 | 2 | 15.00 | 30.00 |

if we update the filter to 6/15/23 I'd expect the following output:
| branch | sku | on_hand | latest_price | inventory_value |
| --- | --- | --- | --- | --- |
| branch1 | sku1 | 7 | 20.00 | 140.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!

Answer

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

branch	sku	date	qty_shipped	qty_received	qty_adjusted	price
branch1	sku1	8/1/23	0	1	0	10.00
branch1	sku1	7/1/23	5	0	0	15.00
branch1	sku1	6/1/23	0	0	7	20.00

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

Relevant content