topic Re: How do I get an average from a Count Distinct? in QlikView

How do I get an average from a Count Distinct?

cliff_clayman — Fri, 16 Mar 2018 23:04:06 GMT

I need to get the average for this expression, but it gives me an error that nested aggregation is not allowed:

=COUNT(DISTINCT([Driver ID]))

How can I get the average for this?

Re: How do I get an average from a Count Distinct?

sunny_talwar — Fri, 16 Mar 2018 23:42:02 GMT

What dimensions are involved here?

Re: How do I get an average from a Count Distinct?

cliff_clayman — Fri, 16 Mar 2018 23:45:45 GMT

Just Month

Re: How do I get an average from a Count Distinct?

sunny_talwar — Fri, 16 Mar 2018 23:47:26 GMT

May be this

Avg(Aggr(Count(DISTINCT [Driver ID]), Month))

Re: How do I get an average from a Count Distinct?

cliff_clayman — Fri, 16 Mar 2018 23:53:42 GMT

That's not returning the expected result. It's much lower than it should be.

Re: How do I get an average from a Count Distinct?

sunny_talwar — Sat, 17 Mar 2018 00:03:13 GMT

I don't think you have provided enough information to help you with a solution. You need to provide more details

Re: How do I get an average from a Count Distinct?

cliff_clayman — Sat, 17 Mar 2018 00:08:57 GMT

I think the issue is that there is another dimension in a sense. I don't have it specified in the expression, but Vendor is needs to be accounted for as well as Month. I have 2 values for Vendor, VendorA and VendorB.

Re: How do I get an average from a Count Distinct?

sunny_talwar — Sat, 17 Mar 2018 00:09:57 GMT

Hahahaha then try to add it and check if that works or not.

Re: How do I get an average from a Count Distinct?

cliff_clayman — Sat, 17 Mar 2018 00:17:30 GMT

Can it be done using Set Analysis?

Re: How do I get an average from a Count Distinct?

sunny_talwar — Sat, 17 Mar 2018 00:20:31 GMT

I don't even know what you have and what you are trying to do... how can I say if set analysis will work or not.

If you don't mind me asking, why are you so vague with your description? Why is there a fear to share a sample

Re: How do I get an average from a Count Distinct?

cliff_clayman — Sat, 17 Mar 2018 00:22:44 GMT

I need to add the value for Vendor into my Count Distinct expression. Yes, I'm being vague because this is for work and I can't share that data.

Re: How do I get an average from a Count Distinct?

sunny_talwar — Sat, 17 Mar 2018 00:26:03 GMT

I think we might have had this discussion 4 times before this.... no one is asking for your confidential proprietary data my friend... all we are asking for is a dummy mocked up data which can help us understand what you have so that we can give you a solution which works for you....

Re: How do I get an average from a Count Distinct?

cliff_clayman — Sat, 17 Mar 2018 00:28:44 GMT

I understand, but it would take a lot to put together a sample qvw for this. I think all I need at this point is to add the Vendor value to the Count expression because when I select the proper Vendor from my Selection Criteria, I get the correct number.

Re: How do I get an average from a Count Distinct?

sunny_talwar — Sat, 17 Mar 2018 00:31:25 GMT

Hahahaha okay.... it will take a lot for me to respond without knowing much.... will let someone else help.

Have a great weekend.

Best,

Sunny

Re: How do I get an average from a Count Distinct?

cliff_clayman — Sat, 17 Mar 2018 00:33:10 GMT

Your initial answer is correct. However, I am missing the Vendor dimension in it. How can I work that into your solution?

Re: How do I get an average from a Count Distinct?

sunny_talwar — Sat, 17 Mar 2018 00:34:44 GMT

You could have not tried this

Avg(Aggr(Count(DISTINCT [Driver ID]), Month, Vendor))

Re: How do I get an average from a Count Distinct?

cliff_clayman — Sat, 17 Mar 2018 00:35:53 GMT

I need to specify the value for Vendor. Vendor = VendorA

Re: How do I get an average from a Count Distinct?

sunny_talwar — Sat, 17 Mar 2018 00:37:51 GMT

Use set analysis for that

Re: How do I get an average from a Count Distinct?

cliff_clayman — Sat, 17 Mar 2018 00:41:08 GMT

Yes, that is what I was asking help for a few questions back.

Re: How do I get an average from a Count Distinct?

sunny_talwar — Sat, 17 Mar 2018 00:44:39 GMT

All you said was this

Can it be done using Set Analysis?

Anyways... add this {<Vendor = {'Vendor A'}>}