I am trying to create a report that is coming from three tables to get the average delivery time by vendor, and I want to summarize the data based on the vendor name
The dimensions are:
Vendors.VendorName
orders.need_by_date (hidden from visual)
receipts.receipt_created_date (hidden from visual)
A custom calculation is created:
diff_days(${receipts.receipt_created_date},${orders.need_by_date})
What I get now is a list of vendors and each order need by date and receipt date and the number of days the receipt was early or late from the need by date.
The report shows:
Vendor 1 1.25
Vendor 1 0
Vendor 1 .5
Vendor 2 2.5
Vendor 2 -5
Vendor 3 0.25
Vendor 3 -2
Etc..
What I am looking to do is summarize this data by Vendor, so the data above would look like this (assuming my math skills are working today):
Vendor 1 .58 Days
Vendor 2. -.833 Days
Vendor 3 -.75 Days
So basically I need to take the total of the Diff Days / Count of orders.need_by_date and display for each supplier.
Is this possible, and if so how would I do this? Add another calculated column? What would force the report to summarize by vendor instead of showing every order line?
LinkedIn
Twitter
