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

    Count and aggr



      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:




      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:



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



      Can somebody help pleas?



        • 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.



            • Re: Count and aggr

              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.



                • 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



                      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




                          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