May 4, 2006 at 7:33 am
Hi
I have been batteling to get the below right. I am fairly new to MDX but managing to keep my head above water.
I am trying to get the average of the Due vs Delivery. What makes this more interesting is that not all the items have been delivered.
Here is some sample information from the underlying fact table.
Department Size Due Date Delivery Date Due vs Delivery Count
01-Mens 0028 13/04/2006 13/04/2006 0 1
01-Mens 0028 13/04/2006 13/04/2006 0 1
01-Mens 0028 13/04/2006 NULL NULL 1
01-Mens 0028 13/04/2006 NULL NULL 1
01-Mens 0028 13/04/2006 NULL NULL 1
01-Mens 0028 18/04/2006 NULL NULL 1
01-Mens 0028 13/04/2006 12/04/2006 -1 1
01-Mens 0028 13/04/2006 12/04/2006 -1 1
01-Mens 0028 13/04/2006 12/04/2006 -1 1
01-Mens 0028 13/04/2006 12/04/2006 -1 1
I am trying to get to an answer where MDX sums the Due vs Delivery and Devides it by the lines that actualy contains some value and not null. The answer should come out to be -4/6 = -0.67
Any help would be much appreciated
Kindest regards
Jacques
May 4, 2006 at 10:29 am
Maybe I'm not getting the point but couldn't you:
- add a new column to what you've already got in your source, called 'Delivered' which has a 1 if delivered date IS NOT NULL
- rename (for me) the 'Due' column to 'Days Late', leave it calculating negatives for early deliveries, zero for ontime and positives where it was late
- Add the 'Days Late' and 'Delivered' to the cube as measures
- then add a calc measure of 'Days Late / Delivered' which should then give you your on time percentage?
Same theory as yours, larger negative numbers are good/better (as long as people don't mind getting things delivered early!)
You could extend this to:
- (again, for me) rename the 'Delivery Count' to 'Deliveries' or maybe 'Orders'
- then add a calculated measure of 'Deliveries / Delivered' to give you an outstanding delivery percentage
You might want to tie this to a date, so it only counts the 'Deliveries' (or 'Orders') where their due date is < today().
Steve.
May 9, 2006 at 4:47 am
Hello Jacques,
I think you can build your MDX query by using the Filter() function in order to ignore the null values.
Take a look here (to see how to use the function):
http://www.databasejournal.com/features/mssql/article.php/10894_3306101_2
Also, the idea of Steve seems to me pretty good.
Athina
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply