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

    Total and average with Value list Dimension

    Denis LOMAKIN

      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

      ))))))))))

        • Re: Total and average with Value list Dimension
          Sunny Talwar

          What is the expected output here?

            • Re: Total and average with Value list Dimension
              Denis LOMAKIN

              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
                  Sunny Talwar

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

                    • Re: Total and average with Value list Dimension
                      Denis LOMAKIN

                      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
                          Sunny Talwar

                          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

                          ))))))))))

                            • Re: Total and average with Value list Dimension
                              Denis LOMAKIN

                              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
                                  Sunny Talwar

                                  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

                                  ))))))))))