3 Replies Latest reply: Aug 27, 2016 9:28 AM by Sunny Talwar RSS

    set analysis, if expression, and dimension filters

    Dave Bauer

      Good Morning (still morning in Hawaii!),

       

      We use Qlik Sense.

       

      I am simplifying this from what we actually did.

       

      Lets say that we had dimensions 1-10.  We wanted our budget numbers to display for dimension 1-3 if filtered on and displayed as 'N/A' if dimensions 4-10 are filtered.  The problem that arose is that we only budget based on dimension 1-3 and not on dimensions 4-10.  When filtering on dimensions 4-10 it was showing a weird budget where there was not one.  So we used set analysis on a sum of the budget number to accomplish this:

       

      if(round((Sum({$<dimension4=,dimension5=,dimension6=,dimension7=,dimension8=,dimension9=,dimension10=>}SalesBudget)))=(round(sumSalesBudget))),sum({$<dimension4=,dimension5=,dimension6=,dimension7=,dimension8=,dimension9=,dimension10=>}SalesBudget),'N/A')

       

      It worked perfectly.  The problem is the list keeps on building as we add more data, so we still want dimensions 1-3 to display a budget number when filtered on, but now we have dimensions 4-20 that need to display 'N/A'.  Is there a way with set analysis to do the inverse of what we are doing?

       

      Any help is appreciated!