topic Re: Distinct Count Issue in QlikView

Distinct Count Issue

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

Hi All,

In one line my requirement is I want to get something like this:

Count (*)

from

(select distinct OrderNumber,OrderLine

from Table);

If you check above pic. I have Two Order no : 042115 and 042116.

For 042115, the distinct count OrderLine = 3 and

for 042116, the distinct count OrderLine = 2.

So in count I need 3+2. and I want plot only this count in Separate Bar Chart.

Could anyone please help me to get this.

Thanks,

Sarif

Re: Distinct Count Issue

avinashelite — Thu, 04 May 2017 14:23:21 GMT

Add a expression like

count(DISTINCT AHPLotID) that should work for you

Re: Distinct Count Issue

mhmmd_srf — Thu, 04 May 2017 14:26:28 GMT

No Avinash... it will give me 8. Not 5.

Re: Distinct Count Issue

avinashelite — Thu, 04 May 2017 14:27:51 GMT

remove the AHPLotID column

Re: Distinct Count Issue

mhmmd_srf — Thu, 04 May 2017 14:32:14 GMT

Hi Avinash,

I need this in single KPI, like Text Box. So I guess we need to handle in expression.

Please help.

Thanks,

Sarif

Re: Distinct Count Issue

Anil_Babu_Samineni — Thu, 04 May 2017 14:33:39 GMT

May be this?

Aggr(Count(DISTINCT OrderLine), OrderNumber)

Re: Distinct Count Issue

avinashelite — Thu, 04 May 2017 14:39:17 GMT

share the sample app let me check your data set and get back to you

Re: Distinct Count Issue

mhmmd_srf — Thu, 04 May 2017 14:39:52 GMT

Hello Anil,

This is not returning anything.

Please help.

Thanks,

Sarif

Re: Distinct Count Issue

vishsaggi — Thu, 04 May 2017 14:40:10 GMT

Try this in text box may be?

= Sum(Aggr(Count(Distinct OrderLine), OrderNo))

Re: Distinct Count Issue

Anil_Babu_Samineni — Thu, 04 May 2017 14:43:47 GMT

Check this then

Sum(Aggr(Count(Distinct OrderLine), OrderNumber))

Re: Distinct Count Issue

mhmmd_srf — Thu, 04 May 2017 15:24:42 GMT

Hello Anil,

Thanks a lot.. it is working fine.

Now If I want this expression with a where clause, like Where Count(AHPLot ID) >0.

How can I make the Set Expression.

Please help.

Thanks,

Sarif

Re: Distinct Count Issue

santiago_respane — Thu, 04 May 2017 15:28:31 GMT

I was going to suggest the same until i saw this post. Vishwarath suggestion should do the trick.

Regards,

Re: Distinct Count Issue

Anil_Babu_Samineni — Thu, 04 May 2017 15:41:39 GMT

How can I make the Set Expression.

May be this

Sum(Aggr(Count({<AHPLotID = {"=Count(AHPLotID) > 0"}>} Distinct OrderLine), OrderNumber))

Note: I request you to mark as helpful and close this thread by flag of Vishwarath Answer.

Re: Distinct Count Issue

Digvijay_Singh — Thu, 04 May 2017 15:45:34 GMT

Bit confusing now, are you sure its Count(AHPLot ID) >0 ? or AHPLot ID >0

Its easy to add AHPLot ID) >0 as set expression inside count function in the suggested expression -

Sum(Aggr(Count(Distinct OrderLine), OrderNumber))