Show all values in Pivot/Straight Table (irrespective of current selection)

    Hello,

     

    There are numerous times I have come across the requirement to show all values of a Dimension in a Pivot or Straight table irrespective of any selection.

     

    Default behaviour of Qlikview is to show only those values that have value with respect to the current selection.

     

    There are times when "Show All Values" option (Show all values in a dimension) doesn't work.

     

    One way of solving this could be create empty rows with all possible dimension value combination which could be a nightmare.

     

    Below workaround can achieve the same with minimal effort.

     

        Original Expression - sum({your set analysis} Sales). Eg - sum({<Year = {"2014"} , Region = {"Americas"}>} Sales)

     

     

         Modified Expression - if (sum({Ignore All dimensions} Sales) <> sum(Sales), sum({Your set analysis} sales, 0)

                                       . Eg if (sum({1} Sales) <> sum(Sales), sum({<Year = {"2014"} , Region = {"Americas"}>} Sales), 0)

     

    I think this makes Qlikview evaluate all the possible dimension values which have Sales (irrespective of any selection).

    The result you get is the one you need as the if clause will always be TRUE(write it such that its always true) and will show 0 values for all the unrelated records.