How to sum the avg of all (4) steps only ?

[yanivvl0]

HELLO , i have ORDERS , each can have maximum 4 steps , step have from and to dates , and a close step can be only a step with one step  a head ( if i have steps 1 2 3 then 1 2 are close and 3 is open ) , if there are 4 steps then all are close :

i have a pivot table that shows for each city (hebrew) the avg (months) time for each step :

avg step 1

= sum ( aggr (  avg  (  Interval(    if ( is_step_Close = 1 and step = 1 , to_step_date - from_step_date  )    ,'d') / 30  ) , city  ,step ) )

i need to sum all steps avg BUT only for the orders with all 4 steps , how can i do that ?

THANKS.

- Sorry I did not mention the following detail : all expresions ment only to CLOSE STEPS.

here attached example of the data .

[sunny_talwar]

Would you be able to share a sample to test this out?

[effinty2112 · Accepted solution]

Hi Yaniv,

As Sunny suggests it would be easier to help you if you can supply a sample. Sometimes that's not so easy. If not then a suggestion for you is to use this expression in set analysis:

{$<Orders = P({<STEP = {4}>})>}

This will restrict the orders included in an aggregation to those that have attained STEP = 4.

the effect is a  bit like selecting 4 in a listbox for STEP, then in a listbox for Orders selecting all possible then deselecting STEP.

Good luck

Andrew

[yanivvl0]

hi Sunny ,  i attached a sample , thanks.

[yanivvl0]

Hi Andrew  - looks OK - great help - THANKS !!