Solved: unable to give conditions in set analysis - Qlik Community

Report Inappropriate Content · ‎2016-12-07

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

sunny_talwar · ‎2017-01-24

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

View solution in original post

tamilarasu · ‎2016-12-07

Hello Jack,

Try this,

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

Report Inappropriate Content · ‎2016-12-07

Thank you Tamil Nagaraj,

How to give DEPT='DEPT1' ,Year=2016 in below condition.

Required output is:

1 C

2 A

=Concat(distinct aggr(If(Flag>0 and Year=2016 and DEPT='DEPT1',count(TOTAL <STATUS>Acctno))&''&STATUS,STATUS),Chr(10))

This is not working.Please help me.

tamilarasu · ‎2016-12-07

Hi Jack,

Have a look at the attached file.

=Concat(Distinct Aggr(If(DEPT = 'DEPT1' And Flag >0 And Year=2016,Count(TOTAL <STATUS>Acctno)&''&STATUS),STATUS),Chr(10))

Report Inappropriate Content · ‎2016-12-07

Hi tamilarasu

It is showing empty when DEPT is unselected. I want to show the value always.

tamilarasu · ‎2016-12-07

Jack,

Do you want to show the count only when the dept = dept1 and Flag >0 and Year=2016 ? And the count should not affect even the selections are removed?

Report Inappropriate Content · ‎2016-12-07

Yes.

jagan · ‎2016-12-07

Hi,

What is the final output you are expecting when no deparment is selected? Can you attach a screenshot?

Regards,

Jagan.

sunny_talwar · ‎2016-12-07

Are you looking to get multiple text box object, one for each department?

Report Inappropriate Content · ‎2016-12-07

Hi Jagan,

I want to show 6 text objects which contains DEP=<Deptno>,Year=2016,Flag=1 filters.

My required output is: