June 30, 2014 at 10:22 am
Hello All,
I am trying to get a percentage of "On Time" shipments for all locations for one week periods.
My company has multiple locations and in three of them shipments are considered "On Time" if they are shipped either "On Time" or "Early".
The others have to ship "On Time" for it to count.
I am able to get the correct information at the location level with this code:
=Switch(First(Fields!PerOnTime.Value)=1,(Sum(Fields!OnTime.Value)+Sum(Fields!Early.Value))/Count(Fields!duedate.Value)
,Fields!PerOnTime.Value=0,(Sum(Fields!OnTime.Value))/Count(Fields!duedate.Value))
The PerOnTime field generates a 1 if the location is equal to one of the locations mentioned above.
Unfortunately, I am unable to replicate this calculation for the whole organization. I am pretty sure that it is a simple logic issue, but I can't seem to get it right.
I want the "Total Percentage On Time" calculation to look like this (written in pseudo code):
((PerOnTime = 1, Sum of Early and On Time) + (PerOnTime = 0, Sum of On Time))/Count(DueDate)
Can anyone point me in the right direction?
Thank you
James L. Eichelberger
June 30, 2014 at 1:08 pm
It's pretty hard to tell exactly what the problem is from this description. Any chance you cold have a read of the article in my signature and provide a sample report with dummy data?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 30, 2014 at 2:32 pm
Here is a sample report with sample data.
I hope that I did this correctly.
I would like the Percentage in Textbox70 to reflect the total percentage of On Time for the organization.
June 30, 2014 at 3:21 pm
Yes, you did a great job of providing a working sample 🙂
What should the correct figure be for that total?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 30, 2014 at 3:30 pm
Thank you for your efforts!
It should be 50%:
6 total entries
1 Early, 1 On Time for LSM
1 On Time for RSM
June 30, 2014 at 3:39 pm
jeichelber (6/30/2014)
Thank you for your efforts!It should be 50%:
6 total entries
1 Early, 1 On Time for LSM
1 On Time for RSM
In that case, you can do this in TextBox70
= Sum(Fields!Early.Value * Fields!PerOnTime.Value + Fields!OnTime.Value)
/ Count(Fields!duedate.Value)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 30, 2014 at 3:54 pm
Thank you very much!
That worked.
I see how you did that.
I just wasn't thinking about the solution that way.
June 30, 2014 at 3:55 pm
You're most welcome, and thanks for making it easy to help you!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply