I have some data that's formatted like this:
The table shows order statuses at 3 different warehouses on 3 different days. For whatever time period the user has filtered to, I need to calculate the percentage of orders that are picked and shipped as follows: SUM([PickedAndShipped])/(SUM([Unfulfilled Orders]) + SUM([PickedNotShipped]) + SUM([PickedAndShipped])). I also need to exclude any days where PickedAndShipped = 0 because those are days when the warehouse is closed and they shouldn't have unfulfilled orders on those days count against them.
This would be easy if the number of orders in each status were all on the same row in their own columns but they're in 3 different rows for each day and warehouse combo.
My question is:
- With the data in the format above, is there a way to calculate the percentage of orders that are picked and shipped
- Keep in mind not every day and warehouse combo will have 3 transaction types so using relative row positions probably won't work
- If not, is there a way to crosstab the data above in Tableau so the number of orders in each status or all in the same row?
- I realize I could do this using a Custom SQL Query but the data is in a CSV file and Tableau does not allow Custom SQL Queries for CSV files)...basically the opposite of this: : Pivot Data from Columns to Rows
- I also can't just convert the CSV to some other format that allows Custom SQL Queries because the CSV file gets updated hourly by a Python script I don't own and I don't know how to recreate the file in the new format on a regular schedule.
I've attached a sample TWBX file using data in the crosstabbed format to build the dashboard I'm trying to create in case that helps. Thanks in advance!