1 Reply Latest reply: May 25, 2017 9:33 AM by Bryan Schnugg RSS

    Want to show every value in a dimension, unless certain filters are used

    Bryan Schnugg

      I have an issue where I have a dimension (a list of accounts) that I would like to show every value for, unless 3 filters specific are used (either individually or as a group). I want the dimension to ignore all other filters on the sheet. I tried setting a measure equal to 1 for all values in the dimension, but that just returns every value in the dimension, where I would need the dimension to filter on the specific dimensions below.


      Here is the formula I currently use to return the total value of our portfolio sales for the given dimension based on the 3 filters that I want. Unfortunately, when I filter on other dimensions available in the sheet, the target dimension is still filtered. Apologies if this formula is terrible, I'm really just figuring this out as I go along.


      Below is the nested if formula catching the specific selection criteria that I would like to include. I have other filters in the sheet and would like to ignore them (see the "[Dimension]=" portions of the formulas as my attempt...it doesn't work).




      if(Profile=GetFieldSelections(Profile) and Profiles=GetFieldSelections(Profiles) and Region=GetFieldSelections(Region) and State=GetFieldSelections(State),



      (sum({$< Profile={'$(=getfieldselections(Profile))'},Profiles={'$(=getfieldselections(Profiles))'},State={'$(=getfieldselections(State))'},Region={'$(=getfieldselections(Region))'},[Filtered Brand]=,[56Marques]=,[Marque Description]=>}

      [Depletion9L R12]  )),


      if(Profile=GetFieldSelections(Profile) and Region=GetFieldSelections(Region) and State=GetFieldSelections(State),



      (sum({$< Profile={'$(=getfieldselections(Profile))'},State={'$(=getfieldselections(State))'},Region={'$(=getfieldselections(Region))'},[Filtered Brand]=,[56Marques]=,[Marque Description]=>}

      [Depletion9L R12]  )),



      if(Profile=GetFieldSelections(Profile) and Region=GetFieldSelections(Region),



      (sum({$< Profile={'$(=getfieldselections(Profile))'}, Region={'$(=getfieldselections(Region))'},[Filtered Brand]=,[56Marques]=,[Marque Description]=>}

      [Depletion9L R12] )),



      if(Profile=GetFieldSelections(Profile) and Profiles=GetFieldSelections(Profiles),



      (sum({$< Profile={'$(=getfieldselections(Profile))'},Profiles={'$(=getfieldselections(Profiles))'},[56Marques]=,[Filtered Brand]=,[Marque Description]=>}

      [Depletion9L R12] )),






      (sum({$< Profile={'$(=getfieldselections(Profile))'},[Filtered Brand]=,[56Marques]=,[Marque Description]=>}

      [Depletion9L R12]  )),






      (sum({$< State={'$(=getfieldselections(State))'},[Filtered Brand]=,[56Marques]=,[Marque Description]=>}

      [Depletion9L R12]  ))))))))



      Thanks for any help you can provide!

        • Re: Want to show every value in a dimension, unless certain filters are used
          Bryan Schnugg

          OK, I figured it out.


          Embarassingly simple, if you use set analysis and set dimentions to ignore to {$<dimension=>} then Qlik ignores selections for that, so the final formula is, in my case:


          sum({$<[Marque Description]=,[56Marques]=,[Filtered Brand]=,CATEGORY=>}  [Depletion9L R12])


          hope this helps someone else, as much as I loved learning the intricacies of nested ifs and getfieldselection use in set analysis, would have saved a lot of screen time just having to type out this one liner.