Count and aggr

[#1]

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

[RedSky001]

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

Mark

[#3]

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!

[danielrozental]

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

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

[#5]

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

[danielrozental · Accepted solution]

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

[#7]

Thanks!

The first one worked. For the pie chart I've used the below and it did the job.

sum(

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

Opportunity)