Qlik Community

Qlik Sense Cloud Discussions

Announcements
Announcement: We have made updates to resolve a Page Not Found error. If you still get this error, please let us know in the Community Corner.
Highlighted
xiyuzw1113
New Contributor III

Count function with group in expression

Dear all, I have a issue of using count function in Qlik sense cloud.

I now have two tables:

table one:

  

Respondent ClientEvent
1Client AEvent A
1Client AEvent A
1Client AEvent A
2Client AEvent A
2Client AEvent A
2Client AEvent A
2Client AEvent A
2Client AEvent A
3Client BEvent A
3Client BEvent A
3Client BEvent A
3Client BEvent A

Table two :

   

ClientEventNo of respondent
Client AEvent A
Client AEvent B6
Client BEvent A
Client BEvent B4
Client CEvent C5
Client CEvent D6

The two tables are linked by key word "Client". In Qlik sense cloud, I am using Pivot table below:

Capture new.PNG

The data in this Pivot table mostly come from table two, which I did not display here. In table two the number of respondent have two value are null, which will get form the table one.

So in the expression I am writing something like this:

=if(match(Client, Client),count({<[table one.Event ]='table two. Event'>}distinct [table one.Respondent_No]), table two no of respondent).

but the results display 0.

Any suggestions??

4 Replies
Digvijay_Singh
Honored Contributor III

Re: Count function with group in expression

Its hard to Identify the problem without trying on the sample data and also its important to know actual field names to understand the association built between two tables, but may be like this -

=if(Count(table two no of respondent)=0,

Aggr(Count({<[table one.Event]={"=[table one.Event]=[table two. Event]"}>}distinct [table one.Respondent_No]),Client),

Count(table two no of respondent))

xiyuzw1113
New Contributor III

Re: Count function with group in expression

Hi Digvijay, thanks for your reply and your suggestion. I have attached the sample data and pivot table(see test.qvf). In the expression I used the suggestion you gave and it didn't work, not sure what the problem, any suggestion?

Thanks!

Re: Count function with group in expression

May be this

Sum(Aggr(Alt([No of respondent], Count(DISTINCT If([Table one.Event] = [Table two.Event], Respondent))), Client_All, [Table one.Event], [Table two.Event]))

Capture.PNG

xiyuzw1113
New Contributor III

Re: Count function with group in expression

Hi Sunny, thanks for your reply. I made some changes to adapt my situation. That really worked!

Thank you so much. This is really made my day!!