Set expression using P() clears selected filters

[shirenmathai]

Hi All,

I have two table in data model, first table contains member details and second table contains memberkey, type and range it belongs to (as shown below).

Member Table :-     

Memeberkey    Address    State

1                        abcd          A

2                        bchg          B

3                        plkjh          C

Range Table :-

Memeberkey    Type         Range   

1                        Gorup1    1 to 200

3                        Gorup1    200 to 500

2                        Gorup2    1 to 200

3                        Gorup2    200 to 500

1                        Gorup2    500 to 1000 

In qlik sense, i have created first sheet with member details. In second sheet i need to show each type in separate table like group1, group 2 etc as shown below. Member in first type belongs to range 1 to 200 might be present in different range in second type let say 500 to 1000.

Type                              Count                                   Type                              Count

Group1 1 to 200               20                                      Group2  1 to 200              60

Group1 200 to 500           40                                      Group2  200 to 500          90

Group1 500 to 1000         50                                      Group2   500 to 1000       60    

My requirement is like when the user select a type let say Group 1 200 to 500 then dashboard should filter based on selected filter means those 40 members should show only in second table and in different range it associated. For that i have used the set expression like  =Count({1<MemberKey=P(MemberKey)>}Status)  and it works as expected

but noticed an issue like if the user select another range in second table then all filter selected previously got cleared. Say i have selected state in first sheet and comes to second sheet and doing above operation will clear state & Group 1 200 to 500 filter. Dashboard then showing only the new range from the second table.

Could you please someone advise how to write set expression for the above scenario. Here i need to keep all the previous selected filters and the filter selected in the sheet.

[sunny_talwar]

I am not completely sure I understand the issue. Would you be able to share a sample to check this out?

[shirenmathai]

Please check the file attached . Select State > KL then go to next sheet, select Group 2 - 1 to 200 range > count will shown as 4 > Just click on Group 1 - 1 to 200 selected state filter will be removed.

[sunny_talwar]

Just changed your dimensions and expressions a little bit and see if this works

[zebhashmi]

I need to understand it better but Maybe this

=Count({1}Memberkey)  in your Group tables.

[pablolabbe]

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

[shirenmathai]

Thank you everyone, i just split the data to two data table based on group and then used in the corresponding table as set expression was not successful.