topic Re: aggr condition in bar chart in QlikView

aggr condition in bar chart

limingthefirst — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a issue in bar chart using aggr function when calculate the no. of vendors for each part and company_site

The dimension is:

=if(aggr(Count(DISTINCT Vendor_Code), Part_Code,Company_Site)=1,

'1 Vendor',

aggr(Count(DISTINCT Vendor_Code), Part_Code,Company_Site)&' Vendors')

And the expression is:

Count(DISTINCT Part_Code)

So the bar chart is below, and link to category detail table. Now the Company Site and Part Number column is not selected

The issue is when click the 2 Vendors in bar chart, 1 Vendor has appeared. Normally, this should not appear.

Owing to the third record, Company Site and Part Number has only one Vendor.

The first and second record is what I want when I click the bar chart.

Can anybody help me to fix this issue? Thanks.

sunny_talwar — Mon, 20 Nov 2017 11:22:25 GMT

Create a new field in the script using this

LOAD AutoNumber(Part_Code&Company_Site) as Key,

and then try this as your dimension

=If(Aggr(Count(DISTINCT Vendor_Code), Key) = 1, '1 Vendor', Aggr(Count(DISTINCT Vendor_Code), Key)&' Vendors')

limingthefirst — Mon, 20 Nov 2017 11:28:03 GMT

Thank Sunny, the Part_Code and Company_Site is in different table, I will try to modify the data model.

sunny_talwar — Mon, 20 Nov 2017 11:29:41 GMT

It might be possible without changing the data model, but it will have to be tested, would you be able to share a sample?

limingthefirst — Mon, 20 Nov 2017 11:59:33 GMT

I have create a demo.qvw, but the issue is it appear 3 Vendors when click the 1 Vendor

limingthefirst — Mon, 20 Nov 2017 12:30:51 GMT

I have change the data model, and it working well, Thank you again.