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

Q&A
, ,
1

Hello I think I asked this in the wrong place on re:Post,

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!

2

Hello @lg2279, welcome to the Quick Sight Community! Iā€™m glad you found the right place to post.

These should all be possible to achieve. For on-hand, since we are checking multiple rows, Iā€™d recommend using sumOver to achieve this. It would look something like this:
sumOver({qty_received}, [], PRE_AGG) - (sumOver({qty_shipped}, [] PRE_AGG) + sumOver({qty_adjusted}, [], PRE_AGG))

sumOver will allow you to check all values across rows. It is also great because if you had multiple branches and you wanted to check for each, you can add {branch} into the partition field to calculate for each.

For latest price using firstValue or lastValue should work great. Iā€™ll build an example of firstValue below:
firstValue([{price}], [{date} DESC])

Then you should be able to get inventory_value with those 2 fields! Iā€™ll mark my response as the solution, but if you have any follow-up questions, please let me know!

3

Hey @DylanM,

Thank you for the quick response! A couple of things:

  1. This would be for a pivot table right?

  2. Do you have any other details on the structure of the analysis?

Currently I have a pivot table with branch, sku, as_of_date, and price (hidden) in the rows field well and on_hand, price, and inventory_value in values field well. After updating the fields according to your suggestions, all skus are returning the same price value for their ā€œlatest as of the filter dateā€.

Also, I think the suggested summing for on_hand is causing the entire dataset to not load aggregated totals for the skus (causing a ā€œvisual errorā€ in Quick Sight) but when adding an available unique transaction_id field drilling down into it, it seems the aggregate is wildly inaccurate suggesting an on hand of > 500k and all skus have the same values.

Iā€™ve used both of the suggested functions as well as lastValue among others while trying to solve this but mostly keep missing on the latest price for the given sku as of the given filter date

4

Hello @lg2279, these calculated fields should work for a pivot table. Also, I am not entirely sure what you mean by your 2nd question. I need a little more detail to help you with that.

Are you saying the latest price isnā€™t updating based on the filter you are applying? If you are filtering the visual it is being displayed in, and it isnā€™t updating the value, we may want to find a different way to accomplish this. It might require using a max() or maxOver() function to check the max date field, then run another calculated field with an ifelse to check if maxDate is equal to current date. That would be an alternative option that should work.

As for the on_hand value, first thing I would try is returning a avg and not a sum. Due to the functionality of the sumOver(), the same value will be returned on many rows so avg, max, or min would be the best way to return that value. If that number is still off, return each piece of the calculation individually so we can check if the numbers being returned make sense before adding/subtracting them.

5

Hey @DylanM,

Are you saying the latest price isnā€™t updating based on the filter you are applying? If you are filtering the visual it is being displayed inā€¦

Right, and itā€™s using the same price for all of the skus. The date field in the dataset is not aggregated so I imagine itā€™d need to be aggregated in order to use either the max() or maxOver() function.

ā€¦then run another calculated field with an ifelse to check if maxDate is equal to current date.

The problem here is that a valid value for price might not be on the record that has the max date but on one before the max date if that makes sense?

As for the on_hand valueā€¦

I believe I was able to fix this and am less worried about this particular field than I am about getting the price field to work as expected

The dataset I provided should be everything thatā€™s required to rebuild and attempt what Iā€™m trying to do within Quick Sight. I donā€™t feel like itā€™s overly complex but it might be QS doesnā€™t have the features necessary to do this at the moment. I think in MySQL this would normally be done with a subquery but I havenā€™t really tried since I wanted to keep the dataset as flat/simple as possible

Thank you again for the continued assistance!

6

Hello @lg2279, I have a thought on the date issue. Is the filter you are applying to the date linked to a parameter value? If so, does the value selected match the value for your date field? If not, you could definitely use truncDate as a work-around to convert both the as_of_date parameter and date field to display just the first day of the month. Then you could have a calculated field that just checks if the date field equals the parameter value.
ifelse(truncDate('MM', ${as_of_date}) = truncDate('MM', {date}), {price}, NULL)

Then you could use something like a maxOver() function for that calculated field to return the only price value remaining on every row. That would allow you to aggregate it against the other calculated field I suggested previously.

I will mark this as a solution, but if you have any further follow-up questions on this or issues implementing it, please let me know!

7

@DylanM

That doesnā€™t solve it either. Weā€™re going to hire a consultant and I was planning on posting back here (and on my re:Post question) with the solution that we end up using.

On the topic of solutions, is it normal for people who arenā€™t the person asking to mark their own answers as solutions? I feel like itā€™s extremely misleading for you to mark your own answer as the solution before Iā€™ve even had a chance to read and respond, especially given that each time the answers were incorrect and/or incomplete.

Thank you again for your time and I hope to have the solution posted back here within a week or so.

1 Like
8

Hello @lg2279, if it is an answer that I feel confident in for the issue that is being faced by the community member, I may mark it as a solution, but always ask for follow-up questions if you run into an issue. Then we can always revisit it and update the solution if necessary, since I donā€™t have a full picture of the data, calculated fields, and visuals you are utilizing. I donā€™t at all mean for it to be misleading, I am just hoping to be helpful and guide you to your desired result!

I also greatly appreciate that you will take the time to post the solution that you are able to build out with the consultant, as it will be helpful to any of the community members that experience the same issue. When you do so, please post a new topic in the community and link to this topic to provide relevant information.

Let me know if you have any further follow-up questions on this for now, otherwise I will archive it and keep an eye out for the post with the solution you are able to build out. Thank you so much for understanding, and good luck building out the solution!

9

Hello @lg2279, since we have not heard back with any remaining questions on this topic, I will go ahead and archive it. If you need continued support on this issue or want to post the solution you end up implementing, please post a new topic in the community and link to this question. That will ensure you are at the top of the priority list for a response from one of our Quick Sight experts. Thank you!