6 Replies Latest reply: Sep 7, 2012 4:57 PM by Linda Diodato RSS

    Count and aggr

    Linda Diodato

      Hi,

       

      I'm trying to get the number of Partners with whom each Customer has Total Sales>0 but in whatever way I turn this calculation around I never seem to get it right.

       

      Here is my last attempt:

       

      count(aggr(sum(Sales),Partner,Customer)>0)

       

      My ultimate goal is to get the sum of opportunities for those Customers that have only one Partner with whom they've made Total Sales>0 and below is the expression where I'd have to embed the above:

       

      sum(

      {< Customer= {"=count(aggr(sum(Sales),Partner, Customer)>0)=1"}> }

      Opportunity)

       

      Can somebody help pleas?

       

      Thanks

        • Re: Count and aggr
          Mark Sheraton

          Can you provide a sample qvw with some sampel data attached?  I think this would help you get more responses.

           

          Mark

            • Re: Count and aggr
              Linda Diodato

              Sorry it took me some time.

               

              Please the sample app attached.

               

              In the straight table on the right I've included a green column showing what the result of the calculation show be should be. As for the pie chart, the one on the right has the correct values hard coded in and shows the results I'm trying to achieve.

               

              Before anyone asks, these aggregations and Green/Yellow/Red statuses cannot be calculated in the load script because they have to be dinamically worked out depending on several other selections.

               

              I'd really appreciate some help here.

               

              Thanks!

                • Re: Count and aggr
                  Daniel Rozental

                  how about this count(distinct {<Sales={">0"}>}Partner) ?

                   

                  You could avoid the distinct if you add a Partner master table with a flag.

                    • Re: Count and aggr
                      Linda Diodato

                      Thanks.

                       

                      The problem is that in the real thing the data is much more granular so the sum(Sales) per Partner/Client is made up of several POs over different Quarters, which is why I'm trying to do the aggregation.

                       

                       

                      As for the distinct problem, in the real app I do have a DistinctPartner table where each equals 1 so that I can go sum(DistinctPartner) rather than count(distinct Partner).

                       

                      The other reason why I need to keep the aggregation is that the pie chart doesn't have any dimension.

                       

                      Any further suggestions?

                       

                      Thank you

                        • Re: Count and aggr
                          Daniel Rozental

                          I still don't see a need to do an aggregation but it will look something like this

                           

                          count(aggr(if(sum(Sales)>0,1),Partner,Client))

                           

                          If you then want to aggregate customers you would need to do something like this

                           

                          sum(aggr(if( count(aggr(if(sum(Sales)>0,1),Partner,Client)) = 1,1) Client))

                           

                          Set analysis wouldnt work in your expressions