Karen Martek
New Member
- Joined
- Apr 23, 2018
- Messages
- 1
Hi,
I have a report generated by Jet which is forming the dynamic basis for some logistics KPI's. I'm 90% done but have one least measurement that sounds simple but has me stumped.
The source data in the report has one line per item shipped, so a single shipment reference could have multiple lines on the source data and one of the fields shows if the line was hazardous or not with a TRUE/FALSE value. For the detailed KPI's this works great I can show how many haz and how many non haz lines were on each shipment per shipper per day
My problem is on the overview data - I want to know how many shipments per shipper were hazardous each day not the number of haz lines. So they may have shipped 10 haz lines but only across 7 orders. I currently have the data pivoted to show shippers as columns (only2-3), then dates and order numbers as row labels. the values are the haz lines (filtered to only show data with a value in this field). My issue is I cant use a count function in the pivot table as its counting the source data not the number of rows with a value in the pivot table.
so a bit like this:
Row labels AHALL PREEVES
03/04/18 5 3
60446 3
62804 2
62804 3
Against the date in the above example I want it to show 2 in the bold sub total for AHALL and 1 for PREEVES.
I did try a solution in the source data to return unique values based on 4 fields to then pivot - but the formula wasn't supported within the jet functions.
any thoughts/ideas greatly appreciated.
I have a report generated by Jet which is forming the dynamic basis for some logistics KPI's. I'm 90% done but have one least measurement that sounds simple but has me stumped.
The source data in the report has one line per item shipped, so a single shipment reference could have multiple lines on the source data and one of the fields shows if the line was hazardous or not with a TRUE/FALSE value. For the detailed KPI's this works great I can show how many haz and how many non haz lines were on each shipment per shipper per day
My problem is on the overview data - I want to know how many shipments per shipper were hazardous each day not the number of haz lines. So they may have shipped 10 haz lines but only across 7 orders. I currently have the data pivoted to show shippers as columns (only2-3), then dates and order numbers as row labels. the values are the haz lines (filtered to only show data with a value in this field). My issue is I cant use a count function in the pivot table as its counting the source data not the number of rows with a value in the pivot table.
so a bit like this:
Row labels AHALL PREEVES
03/04/18 5 3
60446 3
62804 2
62804 3
Against the date in the above example I want it to show 2 in the bold sub total for AHALL and 1 for PREEVES.
I did try a solution in the source data to return unique values based on 4 fields to then pivot - but the formula wasn't supported within the jet functions.
any thoughts/ideas greatly appreciated.
