Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
apthansh
Contributor

get count based on 2 columns

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

1 Solution

Accepted Solutions
jayanttibhe
Contributor III

Re: get count based on 2 columns

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),',')

View solution in original post

6 Replies

Re: get count based on 2 columns

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

jayanttibhe
Contributor III

Re: get count based on 2 columns

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),',')

View solution in original post

apthansh
Contributor

Re: get count based on 2 columns

Thanks much.That worked.

Just wondering Count instead of Sum should also work right ?

sudeepkm
Valued Contributor III

Re: get count based on 2 columns

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
jayanttibhe
Contributor III

Re: get count based on 2 columns

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.

apthansh
Contributor

Re: get count based on 2 columns

Thank you.