Pivot Table - average value for DISTINCT resolvers

[#1]

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,

AM

[#2 · Accepted solution]

Hi, this should be:

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

[#3]

Hi PariPari,

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

Thanks,

AM