Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
deniscamh
Creator
Creator
‎2018-02-12 07:50 PM

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

))))))))))

1,007 Views
0 Likes
8 Replies
sunny_talwar
MVP
MVP
‎2018-02-12 08:17 PM

What is the expected output here?

849 Views
0 Likes
deniscamh
Creator
Creator
‎2018-02-12 08:31 PM
Author

In response to sunny_talwar

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.

849 Views
0 Likes
sunny_talwar
MVP
MVP
‎2018-02-12 08:38 PM

In response to deniscamh

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

849 Views
0 Likes
deniscamh
Creator
Creator
‎2018-02-13 09:37 AM
Author

In response to sunny_talwar

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

849 Views
0 Likes
sunny_talwar
MVP
MVP
‎2018-02-13 09:41 AM

In response to deniscamh

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

))))))))))

849 Views
deniscamh
Creator
Creator
‎2018-02-13 09:46 AM
Author

In response to sunny_talwar

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

849 Views
sunny_talwar
MVP
MVP
‎2018-02-13 09:48 AM

In response to deniscamh

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

))))))))))

849 Views
0 Likes
deniscamh
Creator
Creator
‎2018-02-13 09:57 AM
Author

In response to sunny_talwar

Thanks Sunny,

I will try later today.

Thanks for your help!

849 Views