I have two tables, Orders and Operations. These 2 tables are associated with each one through a field, 'Order' like below:
Orders
---------
Order Date Type_of_Order
100 01/01/2015 A
200 04/09/2015 A
300 02/07/2015 A
400 01/05/2015 A
500 04/02/2016 B
700 28/03/2016 C
800 20/06/2015 A
And so on.... Order cannot be repetead in table Orders.
Operations
---------------
Order Equipment Failure Date_Of_Failure
100 Device01 FailureA 02/02/2015
200 Device04 FailureD2 04/10/2015
250 Device01 FailureA2 18/09/2015
300 Device05 FailureA 03/08/2015
310 Device01 FailureD2 20/04/2016
350 Device04 FailureC 05/12/2015
800 Device03 FailureD1 10/08/2016
and so on....
I select a date range from orders table, for example, from [01/01/2015 to 31/12/2015]. Then for each equipment, I need to calculate the number of days elapsed between first and last failure dates (Field Date_of_failure in table Operations), and then divide it into the number of failures occurred in the range selected [01/01/2015 to 31/12/2015]. Date_Of_Failure should be in the range selected [01/01/2015 to 31/12/2015] in order to calculate the number of days elapsed between first and last failure. Otherwise it is ignored. Also, only TyFinally I need to do the average between the number of equipments.
So for each device I do:
Aggr( Interval(Max({$<[Type_of_Order] = {'A'}>}Date_Of_Failure)-Min({$<[Type_of_Order] = {'A'}>}Date_Of_Failure),'DD') /
if(Count({$<[Type_of_Order] = {'A'}>} [Order]) > 0, Count({$<[Type_of_Order] = {'A'}>} [Order]), 1), Equipment)
)
Note that some device can have no failure between range date selected (as Device03), so when dividing, In order a divide by zero I use a conditional to divide by 1 and not 0 but I would need something else in the expression above to return 0 as a result for equipment Device03.
Above expression calculates for each equipment, but at the end I need to obtain the average between the number of existent equipments. Note that despite calculation for Device03 it should be taken into account as one equipment more so, my final expression is:
=Sum(
Aggr(
Interval(Max({$<[Type_of_Order] = {'A'}>}Date_Of_Failure)-Min({$<[Type_of_Order] = {'A'}>}Date_Of_Failure),'DD') /
if(Count({$<[Type_of_Order] = {'A'}>} [Order]) > 0, Count({$<[Type_of_Order] = {'A'}>} [Order]), 1), Equipment)
)
)
/ Count({$<[Type_of_Order] = {'A'}>} DISTINCT Equipment)
So from the info in the above two tables, if I select range [01/01/2015 to 31/12/2015], the result should be:
Device01 = (18/09/2015 - 02/02/2015) / 2 -> Failure in 20/04/2016 is not taken into account as it is not in the range [01/01/2015 to 31/12/2015]
Device03 = 0 (It should be 0 since it is not in the date range selected [01/01/2015 to 31/12/2015])
Device04 = (05/12/2015 - 04/10/2015) / 2
Device05 = (03/08/2015 - 03/08/2015) / 1 = 0 but in this case it should be 1 (not 0)
and then the final result should be:
2 (Device01) + 0 (Device03) + 2 (Device04) + 1 (Device05) / 4 (Number of existing devices) = 1,25
but I am missing some things in my expression since I am not getting the correct final result.....
Any ideas what am I doing wrong in my above expression?
Also to debug the expression in pieces step by step, I tried to create a straight table with columns (for each equipment): Order, Equipment and calculated dimensions: Min(Date_Of_Failure), Max(Date_Of_Failure), Difference between Min and Max, and number of failures bewteen date range selected. But all calculated columns throw error: Error in calculated dimension....
