Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

unable to give conditions in set analysis

Hi All,

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

Capture.PNG

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

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

22 Replies
tamilarasu
Champion
Champion

Hello Jack,

Try this,

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

Not applicable
Author

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

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

Not applicable
Author

Hi tamilarasu

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

Capture.PNG

tamilarasu
Champion
Champion

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?

Not applicable
Author

Yes.

jagan
Partner
Partner

Hi,

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

Regards,

Jagan.

sunny_talwar

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

Not applicable
Author

Hi Jagan,

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

My required output is:

Capture.PNG