unable to give conditions in set analysis

[#1]

Hi All,

I want to show count(Acctno) for all STATUS in a line for each dept in a text box.

For Dept1 :

1 C

2 A

should not show 5 null count.

For Dept2 :

10 A

1 C

should not show 10 null count.

For Dept3 :

3 A

should not show 4 null count.

I am using this formula:

=Concat(distinct aggr(count(TOTAL <STATUS>Acctno)&''&STATUS,STATUS),Chr(10))

But it is showing NULL count also. I want to ignore them and unable to give the conditions Flag=1 and Deptname in the above set analysis.

Please help me.

vinieme12

jagan mohan rao appala

stalwar1

Celambarasan Adhimulam

[#2]

Hi stalwar1

The below formula is working fine for DEPT=DEPT1 in a text object.

=Concat({<DEPT = {'DEPT1'}, STATUS = {"=Len(Trim(STATUS)) > 0"}>}DISTINCT Aggr(Count({<DEPT = {'DEPT1'}, STATUS = {"=Len(Trim(STATUS)) > 0"}>} TOTAL <STATUS>Acctno)&''&Only({<DEPT = {'DEPT1'}, STATUS = {"=Len(Trim(STATUS)) > 0"}>}STATUS),STATUS),Chr(10))

But not working if I take like below:

Dimension: DEPT

Expression:

=Concat({< STATUS = {"=Len(Trim(STATUS)) > 0"}>}DISTINCT Aggr(Count({< STATUS = {"=Len(Trim(STATUS)) > 0"}>} TOTAL <STATUS>Acctno)&''&Only({< STATUS = {"=Len(Trim(STATUS)) > 0"}>}STATUS),STATUS),Chr(10))

It is showing like this, but not correct.

Required out is like this:

gysbert wassenaar

[sunny_talwar · Accepted solution]

Try this:

=Concat({< STATUS = {"=Len(Trim(STATUS)) > 0"}>}DISTINCT Aggr(Count(Aggr(If(Len(Trim(STATUS)) > 0, Acctno), STATUS, DEPT, Acctno))&''&Only({< STATUS = {"=Len(Trim(STATUS)) > 0"}>}STATUS),STATUS, DEPT),Chr(10))

[#4]

Thank you Sunny bro.