Hi
I have an outcome of my query a string value with 20 characters, e.g. A/P Payments - P00ST. I want to get a trimmed outcome like P00ST, that is trimmed by 15 characters. How to write a query for this?
Regards
Hi,
I have the following table:
OrderNumber varchar(15)
Completed int(1)
Certificate int(1)
ThroughputTime int(4)
With the following data:
OrderNumber Completed Certificate ThroughputTime
00001 1 1 1
00002 1 1 2
00003 1 0 1
00004 1 0 1
00005 0 1 2
00006 0 1 1
00007 0 0 4
00008 0 0 89
I have a dimension:
All Completed
- Completed
- Not Completed
and a dimension:
All Certificate
- No Certificate
- Certificate
I also have a measures:
TptSum which Sums ThroughputTime
TptCount which Counts OrderNumbers
TptAvg which divided TptSum by TptCount
My problem is with the next measure:
In MSAS I created a Cube which shows the measures on the columns and both Completed and Certificate on the rows. The OrderNumber selection is set to "All Ordernumbers". The measures a listed above are displayed exactly as i expect them to do. TptSum gives the sum for the subselection showed on the left. TptCount and TptAvg also show the expected values on their distinct rows.
I can't upload a screenshot but it looks like this:
TptSum TptCount TptAvg
All Certificate All Completed 101 8 12.63
Completed 5 4 1.25
Not Completed 96 4 24.00
No Certificate All Completed 95 4 23.75
Completed 2 2 1.00
Not Completed 93 2 46.50
Certificate All Completed 6 4 1.50
Completed 3 2 1.50
Not Completed 3 2 1.50
So far so good. Each datarow only takes the records that match the criteria at the left.
Now however, To exclude exceptions that have a huge impact on the average I want to create a trimmed average. That is, I want to exclude the top 25% values in my trimmed average measure, which are the 2 records with the highest ThroughputTime, being ordernumber 00007 and 00008 in the "All Certificate" & "All Completed" row, but different in the other rows. I also want the bottom 25% values to be trimmed, which are the 2 records with the lowest ThroughputTime, being ordernumber 00001 and 0003 in the "All Certificate" & "All Completed" row.
I tried a lot of things, but i can't get it working. Somehow all my statements don't take the subselections on the rows into account. Really frustrating.
What I want to achieve is the following:
TptSum TptCount TptAvg TrimmedAvg
All Certificate All Completed 101 8 12.63 1.50 ((2+1+2+1) / 4) (middle 4 records)
Completed 5 4 1.25 1.00 ((1+1) / 2) (middle 2 records)
Not Completed 96 4 24.00 3.00 ((2+4) / 2) (middle 2 records)
No Certificate All Completed 95 4 23.75 2.50 ((1+4) / 2) (middle 2 records)
Completed 2 2 1.00 1.00 ((1+1) / 2) (all 2 records)
Not Completed 93 2 46.50 46.50 ((4+89) / 2) (all 2 records)
Certificate All Completed 6 4 1.50 1.50 ((1+2) / 2) (middle 2 records)
Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records)
Not Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records)
I hope I describe my problem well... I also hope someone knows a solution. I can also post the things I tried, but I'm afraid that makes it more confusing, because nothing worked ;)
Thanks,
Edward
Hi,
I have the following table:
OrderNumber varchar(15)
Completed int(1)
Certificate int(1)
ThroughputTime int(4)
With the following data:
OrderNumber Completed Certificate ThroughputTime
00001 1 1 1
00002 1 1 2
00003 1 0 1
00004 1 0 1
00005 0 1 2
00006 0 1 1
00007 0 0 4
00008 0 0 89
I have a dimension:
All Completed
- Completed
- Not Completed
and a dimension:
All Certificate
- No Certificate
- Certificate
I also have measures:
TptSum which Sums ThroughputTime
TptCount which Counts OrderNumbers
TptAvg which divides TptSum by TptCount
My problem is with the next measure:
In MSAS I created a Cube which shows the measures on the columns and both Completed and Certificate on the rows. The OrderNumber selection is set to "All Ordernumbers". The measures as listed above are displayed exactly as I expect them to do. TptSum gives the sum for the subselection showed on the left. TptCount and TptAvg also show the expected values on their distinct rows.
I can't upload a screenshot but it looks like this:
TptSum TptCount TptAvg
All Certificate All Completed 101 8 12.63
Completed 5 4 1.25
Not Completed 96 4 24.00
No Certificate All Completed 95 4 23.75
Completed 2 2 1.00
Not Completed 93 2 46.50
Certificate All Completed 6 4 1.50
Completed 3 2 1.50
Not Completed 3 2 1.50
So far so good. Each datarow only takes the records that match the criteria at the left.
Now however, To exclude exceptions that have a huge impact on the average I want to create a trimmed average. That is, I want to exclude the top 25% values in my trimmed average measure, which are the 2 records with the highest ThroughputTime, being ordernumber 00007 and 00008 in the "All Certificate" & "All Completed" row, but different in the other rows. I also want the bottom 25% values to be trimmed, which are the 2 records with the lowest ThroughputTime, being ordernumber 00001 and 0003 in the "All Certificate" & "All Completed" row.
I tried a lot of things, but i can't get it working. Somehow all my statements don't take the subselections on the rows into account. Really frustrating.
What I want to achieve is the following:
TptSum TptCount TptAvg TrimmedAvg
All Certificate All Completed 101 8 12.63 1.50 ((2+1+2+1) / 4) (middle 4 records)
Completed 5 4 1.25 1.00 ((1+1) / 2) (middle 2 records)
Not Completed 96 4 24.00 3.00 ((2+4) / 2) (middle 2 records)
No Certificate All Completed 95 4 23.75 2.50 ((1+4) / 2) (middle 2 records)
Completed 2 2 1.00 1.00 ((1+1) / 2) (all 2 records)
Not Completed 93 2 46.50 46.50 ((4+89) / 2) (all 2 records)
Certificate All Completed 6 4 1.50 1.50 ((1+2) / 2) (middle 2 records)
Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records)
Not Completed 3 2 1.50 1.50 ((1+2) / 2) (all 2 records)
I hope I describe my problem well... I also hope someone knows a solution. I can also post the things I tried, but I'm afraid that makes it more confusing, because nothing worked ;)
Thanks,
Edward
PS. newbie alert! :beer:
I am reposting this from the VB IDE forum, becaue I received no response
Using VS05 SP1 Pro/SQL Express...
There are two tables, UserIDs and Recordings (which has a foreign key relating it back to UserIDs).
I created a Stored Procedure via Server Explorer that returns the user ID for a given Foreign Key in Recordings table. If the UserID is "0001", then "0001" is return (userIDs are stored as strings). The stored procedure works every time.
I then created a table adpater that uses the above stored procedure. The table adapter is used in code. It has always worked fine, but i have discovered if the user ID starts with 0, those zeros are trimmed by the table adapter .
For example:
UserID = Me.TableAdapter_Recordings.FillBy_StoredProceedure_Return_UserID(ForeignKey_Recordings_Table).ToString
should result in a userID of "0001", but instead results in "1", which, from a string view point, is incorrect. As strings, "0001" and "1" are totally different, and the "1" fails when you do a fill for the table UserIDs.
So, the Stored Procedure and the Table Adapter using the same Stored Procedure return different results, with the Table Adapter being wrong. Why is it trimming the zeros? Is there anyway to stop that so the results are correct?
Thanks!
Bob
The method I need is TRIMMED MEAN as I need to exclude outliers from an average in the same way as described here:
[URL
I have searched and it appears that Excel has a built-in function for this but DAX does not. What is the best way to do this in PowerPivot?