Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Count and aggr

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

1 Solution

Accepted Solutions
danielrozental
Honored Contributor II

Re: Count and aggr

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

6 Replies
msheraton
Contributor III

Re: Count and aggr

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

Mark

Not applicable

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.

Thanks!

danielrozental
Honored Contributor II

Re: Count and aggr

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

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

Not applicable

Re: Count and aggr

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
Honored Contributor II

Re: Count and aggr

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

Not applicable

Re: Count and aggr

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)

Community Browser