Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

deniscamh
Contributor

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?

Capturetotals.PNG

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

))))))))))

8 Replies

Re: Total and average with Value list Dimension

What is the expected output here?

deniscamh
Contributor

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.

deniscamh
Contributor

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

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

))))))))))

deniscamh
Contributor

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

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

))))))))))

deniscamh
Contributor

Re: Total and average with Value list Dimension

Thanks Sunny,

I will try later today.

Thanks for your help!

Community Browser