topic Re: Avg for limited values in straighttable in QlikView

Avg for limited values in straighttable

alwinsch — Fri, 28 Nov 2014 08:19:23 GMT

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,

Re: Avg for limited values in straighttable

giacomom — Fri, 28 Nov 2014 09:59:55 GMT

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

Re: Avg for limited values in straighttable

alwinsch — Fri, 28 Nov 2014 13:57:37 GMT

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

Thanks it works.

Regards,

Alwin