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]

One text box for each DEPT.

[jagan]

Hi,

Try like this using Straight table with some settings.

Regards,

Jagan.

[sunny_talwar]

Try this for Dept1

=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))

[jagan]

Removed all borders for the cells.

Removed header row.

Text wrap to 3 rows in Presentation tab.

Regards,

Jagan.

[sunny_talwar]

Sample attached

[tamilarasu]

Hi Jack,

May be this?

Dept1:

=Concat({1} Aggr(Count({1<Flag ={'>0'},DEPT = {'DEPT1'}>}Acctno)&''&Only({1<Flag ={'>0'}, DEPT = {'DEPT1'}>}STATUS),STATUS),Chr(10))

You can add year = {'2016'} in the set analysis part.

[tamilarasu]

File attached.

[tamilarasu]

Sunny,

I thought my expression looks lengthy and was waiting for someone to reply with a short & simple solution (I decided to post after seeing yours). Almost both of us have suggested a similar solution.

[#10]

Thank you Jagan, Sunny,Tamil.

[jagan]

Hi,

What if there are 100 Departments?  You will create 100 text objects?

For Sunny's expression you can add Dept name like below so that it is easier to know which department it is showing

='DEPT1:' 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))

Regards,

Jagan.