9 Replies Latest reply: Jan 25, 2018 6:16 AM by Christian Bracchi RSS

    Null measure for specific field(s)

    Christian Bracchi

      Hi All,

       

      I am very new to Qlik Sense.  I have tried many things, but without success.

       

      I have the Field called Brick that contains a number of values (with no standard naming convention).  I would like it so that if the field Brick is used in a table that the call count becomes NULL, i.e. it is not applicable.  Having the total there of 59 is correct, but just not the detail. 

       

      Bricks.PNG

       

      It will be the case that I will have to do the same for other fields also for the same measure, for example another field called Type (This is just a prototype, so please go along with the poor naming conversions for now)

       

      Any help with this would be greatly appreciated.

       

      Thanks,

      Chris

        • Re: Null measure for specific field(s)
          Sunny Talwar

          May be use Dimensionality() function

           

          =If(Dimensionality() = 0, YourExpression, Null())

          • Re: Null measure for specific field(s)
            Darrell Tobin

            Hi Christian,

            If I want to show a dimension that has no data to measure I usually fix this at the data loader (data modelling level).

            For example i have the same issue with Dates.  If no sales occurred on a given date then when reporting in QLik this is not shown, so i lod a table of ALL dates possible and connect this to the measure data.

             

            In your case, the answer is to create a table in the data loader section that has

            1. A field to join to your measure data such as date

            2. All of the brick variables you have

            Join this table to your measure data using the date field and the Brick field can then be selected as the dimension and will show even if you have no measure data ( ensure show values with null is ticked)

              

            DateBrick
            01/01/2018Brick 1
            01/01/2018Brick 2
            01/01/2018Brick 3
            01/01/2018Brick 4
            01/01/2018Brick 5
            01/01/2018Brick 6
            01/01/2018Brick 7
            01/01/2018Brick 8
            01/01/2018Brick 9
            01/01/2018Brick 10
            01/01/2018Brick 11
            01/01/2018Brick 12
            01/01/2018Brick 13
            01/01/2018Brick 14
            02/01/2018Brick 1
            02/01/2018Brick 2
            02/01/2018Brick 3
            02/01/2018Brick 4
            02/01/2018Brick 5
            02/01/2018Brick 6
            02/01/2018Brick 7
            02/01/2018Brick 8
            02/01/2018Brick 9
            02/01/2018Brick 10
            02/01/2018Brick 11
            02/01/2018Brick 12
            02/01/2018Brick 13
            02/01/2018Brick 14
            03/01/2018Brick 1
            03/01/2018Brick 2

            Pls mark complete and like if this is useful

              • Re: Null measure for specific field(s)
                Christian Bracchi

                Hi Darrell,

                 

                Thanks for your help.  Sorry, but I think my question wasn't clear enough.

                 

                If I have a table where someone uses Brick (dimension field), Sales Units and Call Count (measures) then I would like the value for CallCount to be NULL, i.e. don't show anything.

                 

                There is a relationship present in the data model that I do need to keep, so do you know of a solution through some expression that I could use?

                 

                Thanks,

                Chris

                  • Re: Null measure for specific field(s)
                    Darrell Tobin

                    Hi Christian,

                    To the best of my knowledge you cannot create rules to change the way a table behaves based on which fields are added to it by the user.

                     

                    You can make a field show null using the null() function.

                     

                    Below is example:

                    =if(

                         aggr(count(TariffId),Date)=0,                                      //Pre checks if count value is 0

                        null(),                                                                           // If count =0 then shows null instead

                        Count(TariffId)                                                             //else do the count

                    )

                    You may find an extension to help in Branch, but I doubt it.

                     

                    Darrell

                • Re: Null measure for specific field(s)
                  Andrea Gigliotti

                  what's the expression you are using for "Call Count" column?

                  what's your expected result?

                    • Re: Null measure for specific field(s)
                      Christian Bracchi

                      Hi Andrea,

                       

                      current expression is simply

                      Count([%Calls Key]) - this is simply meant to be a count of the rows in a table.  This could equally be a SUM(CallCount) where CallCount is simply a value of 1 per row in that calls fact table.

                       

                      In terms of expected results. if Brick field is EVER in a table/Chart and used with CallCount then CallCount should be NULL.

                       

                      Thanks,

                      Chris