6 Replies Latest reply: Aug 6, 2018 4:37 PM by Richard Fels RSS

    0 instead of Null for Date Dimensions / Set Analysis for Dimensions?

    Richard Fels

      Hi all,

       

      I have an issue with which I struggle and I hope to find help here.

       

      I have a date dimension, associated to other dimensions and values. If filtered, I want to keep all dates in the date dimension.

       

      This is my data structure

       

      MyDate

      Dim

      Stock

      01.01.2018

      a

      5

      02.01.2018

      a

      7

      03.01.2018

      a

      12

      04.01.2018

      a

      3

      05.01.2018

      a

      15

      03.01.2018

      b

      20

      05.01.2018

      b

      50

       

       

      As you can see, the Dim “a” has values on each date (1.1. – 5.1.). Dimension ‘b’ however has no values on 1.1., 2.1. and 4.1.2018.

       

      I would like to have a KPI visualization in Qlik Sense which, when filtered only by Dim “b” returns as min Stock value by date the number 0, as there has been no Stocks on the 1.1., 2.1, and 4.1 (currently, due to the filter and the NULLs for the other dates, I get 20 as a min value when filtering "b". Please also note that I achieved this in a table visualization via the Set expression "sum(Stock) + 0*Sum({1} Stock)", but I need this in a KPI visualization, i.e. a scalar).

      The KPI should furthermore be filterable by date, i.e. if I filter “b” and the date 5.1.2018 I would like the min to be 50 (and not 0).

       

      Help would be greatly appreciated