9 Replies Latest reply on Jul 16, 2018 11:30 AM by Alex Kerin

    ZN function is not working

    suman kumar

      Hello Legends,

       

      I'm trying to use 'ZN' function in my view but it's not working.

      I don't know why but It's not working at all. Tried multiple times. Currently I'm using Tableau 2018.1

      Here I have attached screen shot as well. Please take a look and guide me where I'm wrong.

       

      Screen Shot 2018-07-16 at 5.30.34 PM.png

      You can see under red circle, there should be 0 instead of blank but nothing.

       

      Thanks

      Suman

        • 1. Re: ZN function is not working
          Naveen B

          Hi Suman,

           

          use

           

          zn(lookup(sum([Measure]), 0))

           

          Partitioning and Addressing:

           

          The lookup function is a table calculation in this formula. By default, it should be computed using either Table (Down) or Table (Across).

           

          It will help you kindly mark this answer as correct or helpful so that it will helpful for others

          • 2. Re: ZN function is not working
            Mahfooj Khan

            Hi Suman,

             

            Several options are there to handle the nulls

             

            Option1:

            ZN(LOOKUP(SUM([Your Measure]),0))

             

            Option2:

            IFNULL([Your Measure],0)

             

            Let us know if this help else share some sample mock up data in packaged workbook.

             

            Mahfooj

            • 3. Re: ZN function is not working
              Ritesh Bisht

              Hi Suman,

               

              If you select individually the particular quarter or say month where copier is not there, you can notice that it is not even there in the picture under Sub-category and ZN will act upon a record having a Null value not where there is no sub-category

               

              Screen Shot 2018-07-16 at 5.54.38 PM.png

               

              Thats what I think.

               

              Thanks,

              Ritesh

              • 4. Re: ZN function is not working
                suman kumar

                Hi Everyone,

                 

                Thanks for your quick response.

                But in Tableau, it shows that directly use ZN function and it should work.

                Screen Shot 2018-07-16 at 6.16.13 PM.png

                Can you all please tell me that why it's not working.

                 

                 

                Thanks

                Suman

                • 5. Re: ZN function is not working
                  Naveen B

                  Hi Suman,

                   

                  Consider an example following dataset

                   

                   

                  based on the data set there is value populated as 'Null' for Jan when you use zn(measure)

                  but when you see for Category B - Feb and Category C - Jan , Feb there is no data in the data base itself which means there is no transaction happened on that day in order populate the data which is not in the database what we usually do is we will lookup the current value and populate with zero

                   

                  Hope this helps,

                  if It will help you kindly mark this answer as correct or helpful so that it will helpful for others

                   

                   

                   

                   

                  huhsfauhfsa

                  1 of 1 people found this helpful
                  • 6. Re: ZN function is not working
                    Alex Kerin

                    zn is a row level calculation - so if you have a row in your datasource (not view) that has a measure with no entry (null), then zn will change that to 0. However, it would seem that you have no rows in the datasource that match that breakdown with dimension, so there's no row for zn to operate on. The table calculation option above should work.

                    1 of 1 people found this helpful
                    • 7. Re: ZN function is not working
                      Ritesh Bisht

                      See my screenshot , there should be 'Copier' there to be converted to 0 at first  (Year 2014 and Quarter Q1)....You don't have that Sub-category in the View itself  ..

                       

                      Thanks,

                      Ritesh

                      • 8. Re: ZN function is not working
                        Alan Perez

                        You can solve that problem with a formula:

                         

                        if profit  then protif else 0 end

                         

                        regards

                        • 9. Re: ZN function is not working
                          Alex Kerin

                          Doesn't work if there's no row in the DB that matches the break-down of data.

                           

                           

                          MonthCityProfit
                          JanuaryA45
                          JanuaryB12
                          FebruaryA0
                          FebruaryB
                          MarchA77
                          MarchB87
                          AprilA90
                          MayA33
                          MayB22

                           

                          In this case there is a row for City B in February, so a zn(profit), isnull(profit,0), or if profit  then protif else 0 end methodology works.

                           

                          However, there is no row for City B in April, so these row level formulas do not work (see attached) to remove the blank space.

                           

                          The only method that works is via the table calculation methodology which is able to pad out the data. ZN(LOOKUP(SUM([Profit]),0))