6 Replies Latest reply: Mar 26, 2018 4:20 AM by Shiren Mathai RSS

    Set expression using P() clears selected filters

    Shiren Mathai

      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.