Avg for limited values in straighttable

[alwinsch]

Hi all,

Need some help.

I have 2 tables:

- Salesperson (Id, name)

- Orders (linked to Salesperson table, CreateDate, ClosingDate, Status = Open or Closed)

Now i want a straightTable, with only values from closed orders.

And for these values i want to know the days between closingDate Vs. CreateDate.

Actually, the average value per salesperson.

How can i achieve this properly?

Thanks,

A.

[giacomom · Accepted solution]

Hi Alwin,

you can use Set Analysis to achieve that.

If your dimension is for example the invoice number, the expression to calculate the days between the two dates only for those values that have 'Closed' as their status can be:

1. if you want to calculate without excluding any holidays

     =Only({$ <Status={Closed}>} fabs(ClosingDate - CreateDate))

2. if you want to calculate just working days, then use:

     =Only({$ <Status={Closed}>} fabs(Networkdays(ClosingDate - CreateDate)))

If you want to calculate the average value (of days between the two days?) per salesperson, for example in another straight table with the Salesperson number as a dimension you can use:

     =Avg({$ <Status={Closed}>} fabs(ClosingDate - CreateDate))

Below I attached an example, I hope it is clear.

Regards,

Giacomo

[alwinsch]

is it this simple, i tought i had to use something with aggr function.

Thanks it works.

Regards,

Alwin