topic Re: get count based on 2 columns in QlikView

get count based on 2 columns

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

How to get count based on 2 iD's.

Coulm A Column B

1 aa

1 bb

1 cc

2 dd

3 ff

How to get count of columnB for each Id in Column A

Thank you much

Re: get count based on 2 columns

sunny_talwar — Wed, 10 Oct 2018 13:55:52 GMT

May be add ColumnA as dimension and Count(ColumnB) as expression

Re: get count based on 2 columns

jayanttibhe — Wed, 10 Oct 2018 14:02:41 GMT

Sum(aggr(Count(ColumnB),ColumnA))

You can verify it in text Box if you want to display it outside chart -

Concat(aggr(Count(Distinct [ColumnB]),ColumnA),',')

Re: get count based on 2 columns

apthansh — Wed, 10 Oct 2018 14:13:26 GMT

Thanks much.That worked.

Just wondering Count instead of Sum should also work right ?

Re: get count based on 2 columns

sudeepkm — Wed, 10 Oct 2018 14:19:32 GMT

in case you want to show the Column A value along with the count of Column B per Column A in a text object then below expression can be used.

concat(Aggr([Coulm A]&' - '&count(DISTINCT [Column B]),[Coulm A]),' | ')

output:

1 - 3 | 2 - 1 | 3 - 1

Re: get count based on 2 columns

jayanttibhe — Wed, 10 Oct 2018 14:25:33 GMT

Yes - it depending on which aggregation function you want to use - Sum / count / min / max etc.. the outer Sum in first example is for summation of the aggregated results. You can play around it.

Re: get count based on 2 columns

apthansh — Wed, 17 Oct 2018 19:18:11 GMT

Thank you.