Aggregation issue

Wed, 25 Nov 2020 16:16:04 GMT

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....

Re: Aggregation issue

sunny_talwar — Mon, 29 Aug 2016 16:37:36 GMT

When you create a chart with Equipment as your dimension and below expression

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)

Do you get

Device01 = (18/09/2015 - 02/02/2015) / 2

Device03 = 0

Device04 = (05/12/2015 - 04/10/2015) / 2

Device05 = (03/08/2015 - 03/08/2015) / 1

If you are able to achieve this, then may be all you need is this:

Avg(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))

topic Aggregation issue in QlikView

Aggregation issue

Re: Aggregation issue