8 Replies Latest reply: Feb 13, 2018 9:57 AM by Denis LOMAKIN

# Total and average with Value list Dimension

Hi All,

I have a table like below with valuelist as dimension .

My problem is with a total and average.

I need the total sums a values of rows above instead doing the calculation from the model.

My calculation is count distinct donors but in total I want to sum the donors that were solicited by more than one solicitor.

I tried with rangesum and above but it did not work.

Any suggestions?

My calculation is like this:

count({<FIRST_NAME = {'Jessica'}>} total distinct Prospects_ID),

count({<FIRST_NAME = {'Adrianne'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Molly'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Sandra'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Jordanna'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Kirstin'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Kate'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Tammy'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy'}>} total distinct  "Prospects_ID")

,

(count({<FIRST_NAME = {'Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy'}>} total distinct "Prospects_ID"))/8

))))))))))

• ###### Re: Total and average with Value list Dimension

What is the expected output here?

• ###### Re: Total and average with Value list Dimension

In Total it should be sum of the rows, so the total should be 3327 instead of 3241.

Now the total counting number of distinct donors and if one donor was solicited by more than 1 solicitors in counts one but I need it to count one per solicitor.

Hope make sense.

• ###### Re: Total and average with Value list Dimension

It does, but I am not sure why this is happening.

• ###### Re: Total and average with Value list Dimension

Thanks Sunny,

I know why it is happening, because fir Total I am counting number of donors for all Solicitors and if two donors solicited same donor it counts one but I need to count it two.

My original question was if there is a way to use something like rangesum above or peek function with valuelist as dimension? So the total will get the actual value from rows above and sum them.

Do you know the answer for that?

Thanks

• ###### Re: Total and average with Value list Dimension

count({<FIRST_NAME = {'Jessica'}>} total distinct Prospects_ID),

count({<FIRST_NAME = {'Adrianne'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Molly'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Sandra'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Jordanna'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Kirstin'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Kate'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Tammy'}>} total distinct "Prospects_ID"),

RangeSum(

count({<FIRST_NAME = {'Jessica'}>} total distinct Prospects_ID),

count({<FIRST_NAME = {'Adrianne'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Molly'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Sandra'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Jordanna'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Kirstin'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Kate'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Tammy'}>} total distinct "Prospects_ID")),

(count({<FIRST_NAME = {'Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy'}>} total distinct "Prospects_ID"))/8

))))))))))

• ###### Re: Total and average with Value list Dimension

Thank you for a quick response Sunny.

This will probably work.

I was hoping that there is shorter and more intelligent way.

I cannot check it right now as I am with other client, will check it later.

Thanks

• ###### Re: Total and average with Value list Dimension

This may or may not work... but worth trying

count({<FIRST_NAME = {'Jessica'}>} total distinct Prospects_ID),

count({<FIRST_NAME = {'Adrianne'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Molly'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Sandra'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Jordanna'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Kirstin'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Kate'}>} total distinct "Prospects_ID"),

count({<FIRST_NAME = {'Tammy'}>} total distinct "Prospects_ID"),

Sum({<FIRST_NAME = {'Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy'}>}TOTAL Aggr(count(distinct Prospects_ID), FIRST_NAME)),