how to count in dimension

[Tarig]

Hello

I have two tables here, the first table shows the YearQtr, due date and the count for the distinct memberIds who did not declared fund by yearQtr. the second table is showing the memberId. What I need is when I click on 73 (First Row in Fund summary table), I would be able the see only the 73  memberIds on the Row Data table. The expression I used to  calculate the Not Declared column is: 

aggr( Count({<IsFundReported = {"0"}, InvoiceHeaderId -={NULL}>} DISTINCT MemberId ),YearQtr)

Note: I calculated the Not Declared column as dimension so can be clickable. 

The way is working now I'm getting all MemberId for all YearQtr  (Which is what I need) but when click on the 73 qlik filter for all memberIds for YearQtr 20201 not the 73 and I think I has something to do with the expression I used.

[Lauri]

I would put your expression - Count({<IsFundReported = {"0"}, InvoiceHeaderId -={NULL}>} DISTINCT MemberId )) - as a measure into both tables (and remove the Aggr expression). Then when you click 20201, you'll see the 73 MemberIds.

There is no relationship between the YearQtr and those specific 73 MemberIds. When you click '73' you are simply selecting the 20201 value of YearQtr, which links to all MemberIds in that period. 

[Tarig]

Thank you Lauri for your suggestion, If I applied the expression on both table that means i'm not going to see all members.  

[Lauri]

I bet someone has a more elegant solution, but here's an idea:

1. Add another dimension to your Row Data table with this value: If(IsFundReported = 0 and IsNull(InvoiceHeaderId)=0, MemberId)
2. Set its "Show column if" to GetSelectedCount(YearQtr)>0
3. For the simple MemberId dimension, set the "Show column if" to GetSelectedCount(YearQtr)=0
4. Instruct the user to click on YearQtr value in the other table to see the MemberIds.

[Tarig]

This a great solution if I have one column [Not Declared] but I'm going to be adding other columns  as well. I mentioned one column so I can make it easy for everyone to understand my problem :). but really appreciate you.