topic Strange behaviour when using aggr within sum function in QlikView

Strange behaviour when using aggr within sum function

Wed, 25 Nov 2020 16:16:04 GMT

I have below expression:

=Sum(

Aggr(

if(

Count({$<[Order Type] = {'A'},Actual_Start_Date={'>=$(=Min(Basic_Start_Date))<=$(=Max(Basic_Start_Date))'}>} Order) > 1

(

Interval(Max(Basic_Start_Date) - Min({$<[Order Type] = {'A'},Actual_Start_Date={'>=$(=Min(Basic_Start_Date))<=$(=Max(Basic_Start_Date))'}>} Actual_Start_Date), 'h')

) / Count({$<[Order Type] = {'A'},Actual_Start_Date={'>=$(=Min(Basic_Start_Date))<=$(=Max(Basic_Start_Date))'}>} Order)

if (

Count({$<[Order Type] = {'A'},Actual_Start_Date={'>=$(=Min(Basic_Start_Date))<=$(=Max(Basic_Start_Date))'}>} Order) = 0

, Interval(Max(Basic_Start_Date) - Min(Basic_Start_Date), 'h')

, Interval(Max(Basic_Start_Date) - Min(Basic_Start_Date), 'h') / 2

)

, [Equipment]

)

/ Count({$<[Order Type] = {'A'}, [Equipment]={"=Len(Trim([Equipment]))>0"}>} DISTINCT [Equipment])

For each equipment I perfom a calculation (Aggr) and then using Sum function I obtain the summation of all equipments results. Finally I divide this summation by the number of equipments.

What I see is that it seems like Sum is dividing automatically by the number of equipments once summation is done so at the end the result is divided twice by the number of equipments (that done by Sum "automatically" and that done using the final Count). Could anyone confirm me this?

Note: Using interval I obtain the difference between two dates in hours.

Re: Strange behaviour when using aggr within sum function

sunny_talwar — Tue, 30 Aug 2016 19:33:50 GMT

Tony TP wrote:

What I see is that it seems like Sum is dividing automatically by the number of equipments once summation is done so at the end the result is divided twice by the number of equipments (that done by Sum "automatically" and that done using the final Count). Could anyone confirm me this?

Not sure I understand this, can you elaborate?

Re: Strange behaviour when using aggr within sum function

Tue, 30 Aug 2016 21:52:56 GMT

I mean that it seems like Sum is doing the average, that is, it is dividing the result returned by Aggr function by the number of Equipments. But I do not want this because I do this using the last sentence:

Count({$<[Order Type] = {'A'}, [Equipment]={"=Len(Trim([Equipment]))>0"}>} DISTINCT [Equipment])

So I would like to know if Sum function does the average automatically when used in combination with Aggr. This is what I have observed but I do not know why Sum function behaves in this way.

Re: Strange behaviour when using aggr within sum function

sunny_talwar — Tue, 30 Aug 2016 21:59:33 GMT

I see you are some doing some divisions with count (not the count of Equipments), could that be the reason?

=Sum(

Aggr(

if(

Count({$<[Order Type] = {'A'},Actual_Start_Date={'>=$(=Min(Basic_Start_Date))<=$(=Max(Basic_Start_Date))'}>} Order) > 1

(

Interval(Max(Basic_Start_Date) - Min({$<[Order Type] = {'A'},Actual_Start_Date={'>=$(=Min(Basic_Start_Date))<=$(=Max(Basic_Start_Date))'}>} Actual_Start_Date), 'h')

) / Count({$<[Order Type] = {'A'},Actual_Start_Date={'>=$(=Min(Basic_Start_Date))<=$(=Max(Basic_Start_Date))'}>} Order)

if (

Count({$<[Order Type] = {'A'},Actual_Start_Date={'>=$(=Min(Basic_Start_Date))<=$(=Max(Basic_Start_Date))'}>} Order) = 0

, Interval(Max(Basic_Start_Date) - Min(Basic_Start_Date), 'h')

, Interval(Max(Basic_Start_Date) - Min(Basic_Start_Date), 'h') / 2

)

, [Equipment]

)

/ Count({$<[Order Type] = {'A'}, [Equipment]={"=Len(Trim([Equipment]))>0"}>} DISTINCT [Equipment])

Could that be causing issues? I don't know how these expressions work, so may be create a straight table to create an expression and break them into pieces and see which part isn't working the way you would expect