Pivot Table - average value for DISTINCT resolvers

Mon, 16 Jul 2012 19:11:22 GMT

Hi,

I have calculated 14 day average for a Backlog Calculation using the following expression

=count({<

AsOfDate={">$(=date(now()-14))"},

[Status] = {'Assigned', 'Work In Progress', 'Pending'},

[Pending Date/Time] = {''}

>} [Case ID])/14

The Dimensional Expression is Static string field I created in the script called 'TwoWeeks'.

The expression above adds all of the tickets for the last 14 days divided by 14 correctly giving me the average. So the Pivot Table looks like the following:

TwoWeeks

Backlog 100.0

So far so good.

Now I am trying to calculate 14 day average for DISTINCT Resolvers, who have closed/resolved tickets using the following expression

=count({<

AsOfDate={">$(=date(now()-14))"},

[Status] = {'Resolved'}

>} Distinct [Resolver])/14

with the same dimensional expression as above.

Ideally it should add the number of distinct resolvers for each day (e.g. for a 4 day avg (say)(5+2+1+2)/4 and return 2.5 or 2).

But it is yielding a much lower value.

So, my question is how can I modify this expression to get a 14 day avg.

I would highly appreciate any help on this.

P.S. The expression above for Resolvers (without the "/14") yields correct individual values when displayed in a pivot table, for last 14 days, with the dimensional expression being AsOfDate.

Thanks,

Re: Pivot Table - average value for DISTINCT resolvers

Tue, 17 Jul 2012 08:50:27 GMT

Hi, this should be:

=sum(aggr(count({<AsOfDate={">$(=date(now()-14))"},[Status] = {'Resolved'}>}distinct Resolver),AsOfDate))/14

Re: Pivot Table - average value for DISTINCT resolvers

Tue, 17 Jul 2012 12:49:59 GMT

Hi PariPari,

It worked like a charm. Thank you so much, was really stuck on this one for the last couple of days.

Thanks,

topic Re: Pivot Table - average value for DISTINCT resolvers in QlikView

Pivot Table - average value for DISTINCT resolvers

Re: Pivot Table - average value for DISTINCT resolvers

Re: Pivot Table - average value for DISTINCT resolvers