Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
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))
Thank you Sunny bro.