topic Re: Multiple counts in QlikView

Multiple counts

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

My data is like this:

Data:

Load * inline [

AcctNo,Status

1,A

2,B

3,B

4,D

5,C

6,B

7,A

8,B] ;

I want to show

Count({<Status='A'>}AcctNo)

Count({<Status='B'>}AcctNo)

Count({<Status='C'>}AcctNo)

Count({<Status='D'>}AcctNo) Output like below dynamically in a text box.

2 A

4 B

1 C

1 D

How to achieve this?

Thanks in advance.

Re: Multiple counts

vinieme12 — Mon, 05 Dec 2016 13:08:59 GMT

A Text box wouldn't be dynamic ! what exactly do you want to be dynamic?

=Count({<Status='A'>}AcctNo) & ' A' & chr(10) &

Count({<Status='B'>}AcctNo) & ' B' & chr(10) &

Count({<Status='C'>}AcctNo) & ' C' & chr(10) &

Count({<Status='D'>}AcctNo) & ' D'

Re: Multiple counts

vinieme12 — Mon, 05 Dec 2016 13:11:58 GMT

try this in a text box

=Concat(DISTINCT AGGR(count(TOTAL <Status> AcctNo) & ' ' & Status,Status)

, chr(10))

Re: Multiple counts

Mon, 05 Dec 2016 13:14:11 GMT

Hi Vineeth, If Status is not there, it should be shown.

For example, Status D won't be available December month.How to make it dynamic?

Re: Multiple counts

vinieme12 — Mon, 05 Dec 2016 13:16:55 GMT

try this in a text box

=Concat(DISTINCT AGGR(count(TOTAL <Status> AcctNo) & ' ' & Status,Status)

, chr(10))

Re: Multiple counts

sunny_talwar — Mon, 05 Dec 2016 13:22:16 GMT

Although this might be a small typo, but you guys are missing curly brackets in your set modifier

Besides what are we trying to achieve here?

Re: Multiple counts

Mon, 05 Dec 2016 13:23:58 GMT

How to omit NULLs or '-'values of Status.

Re: Multiple counts

sunny_talwar — Mon, 05 Dec 2016 13:25:27 GMT

May be this:

=Concat({<Status = {'*'}>} DISTINCT AGGR(Count({<Status = {'*'}>} TOTAL <Status> AcctNo) & ' ' & Status,Status), Chr(10))

Re: Multiple counts

Mon, 05 Dec 2016 13:27:20 GMT

Hi Sunny, Vineeth formula is working.

But how to omit NULL status as shown :

Load * inline [

AcctNo,Status

1,A

2,B

3,B

4,D

5,C

6,B

7,A

8,B

10,

11,

12] ;

Re: Multiple counts

vinieme12 — Mon, 05 Dec 2016 13:28:52 GMT

They don't have a status they won't be counted,

Have u tried?

Re: Multiple counts

Mon, 05 Dec 2016 13:31:38 GMT

Yes, I tried. It is showing NULLs.

Re: Multiple counts

sunny_talwar — Mon, 05 Dec 2016 13:33:14 GMT

Did you try this?

=Concat({<Status = {'*'}>} DISTINCT AGGR(Count({<Status = {'*'}>} TOTAL <Status> AcctNo) & ' ' & Status,Status), Chr(10))

or are these not true nulls?