Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Jessica',
count({<FIRST_NAME = {'Jessica'}>} total distinct Prospects_ID),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Adrianne',
count({<FIRST_NAME = {'Adrianne'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Molly',
count({<FIRST_NAME = {'Molly'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Sandra',
count({<FIRST_NAME = {'Sandra'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Jordanna',
count({<FIRST_NAME = {'Jordanna'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Kirstin',
count({<FIRST_NAME = {'Kirstin'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Kate',
count({<FIRST_NAME = {'Kate'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Tammy',
count({<FIRST_NAME = {'Tammy'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Total',
count({<FIRST_NAME = {'Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy'}>} total distinct "Prospects_ID")
,
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Monthly AVG',
(count({<FIRST_NAME = {'Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy'}>} total distinct "Prospects_ID"))/8
))))))))))
What is the expected output here?
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.
It does, but I am not sure why this is happening.
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
How about this?
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Jessica',
count({<FIRST_NAME = {'Jessica'}>} total distinct Prospects_ID),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Adrianne',
count({<FIRST_NAME = {'Adrianne'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Molly',
count({<FIRST_NAME = {'Molly'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Sandra',
count({<FIRST_NAME = {'Sandra'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Jordanna',
count({<FIRST_NAME = {'Jordanna'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Kirstin',
count({<FIRST_NAME = {'Kirstin'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Kate',
count({<FIRST_NAME = {'Kate'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Tammy',
count({<FIRST_NAME = {'Tammy'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Total',
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")),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Monthly AVG',
(count({<FIRST_NAME = {'Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy'}>} total distinct "Prospects_ID"))/8
))))))))))
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
This may or may not work... but worth trying
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Jessica',
count({<FIRST_NAME = {'Jessica'}>} total distinct Prospects_ID),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Adrianne',
count({<FIRST_NAME = {'Adrianne'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Molly',
count({<FIRST_NAME = {'Molly'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Sandra',
count({<FIRST_NAME = {'Sandra'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Jordanna',
count({<FIRST_NAME = {'Jordanna'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Kirstin',
count({<FIRST_NAME = {'Kirstin'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Kate',
count({<FIRST_NAME = {'Kate'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Tammy',
count({<FIRST_NAME = {'Tammy'}>} total distinct "Prospects_ID"),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Total',
Sum({<FIRST_NAME = {'Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy'}>}TOTAL Aggr(count(distinct Prospects_ID), FIRST_NAME)),
if(Valuelist('Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy','Total','Monthly AVG') = 'Monthly AVG',
(count({<FIRST_NAME = {'Jessica','Adrianne','Molly','Sandra','Jordanna','Kirstin','Kate','Tammy'}>} total distinct "Prospects_ID"))/8
))))))))))
Thanks Sunny,
I will try later today.
Thanks for your help!