Multiple counts

[#1]

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.

[vinieme12]

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'

[vinieme12]

try this in a text box

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

  , chr(10))

[#4]

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?

[vinieme12]

try this in a text box

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

  , chr(10))

[sunny_talwar]

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?

[#7]

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

[sunny_talwar · Accepted solution]

May be this:

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

[#9]

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

9,

10,

11,

12] ;

[vinieme12]

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

Have u tried?