1 Reply Latest reply on Apr 23, 2018 9:59 PM by ye li

    "Interesting" Impact of Data Type on Null Value

    ye li

      Recently I met an interesting, well, actually painful situation which made me spend a whole night to figure out and realize the great impact of data type on null value:

       

      I had a data set included fields Days to Conversion, and Conversion (binary). To measure the performance better I created a new filed called Conversion Within 30 Days, and here is formula:

      if Conversion=1 and Days to Conversion<31 then 1 else 0 end.

      However, as I compared the total number of Conversion Within 30 Days to the number I got in excel, I found mine was larger than that....

       

      Then my long dark day came. Here is the process: when creating a new calculating field, Days to Conversion must be an integer instead of string, or the calculation will be invalid. So I changed its data type from string to number. This was the culprit. I did not get the correct total <31 number until I duplicated Days to Conversion, and set the data type one string and one number.

       

      But I still feel very confused and I would appreciate it if anyone can help answer that why one string and one number worked in my case? In the new field calculation, I still used number (data type) for Days to Conversion to make the formula valid, and I did not use Days to Conversion String at all (as column, row, filter or measure value...).

       

      One thing I need to mention is that when data type is string, there is a value "-"; and when data type is number, it became Null. My guess is that without string field, Days to Conversion<31 includes null value, so the new field Conversion Within 30 Days is actually the numbers of days <31 and when the day is null. But why? Why whether null value is included or not is determined by whether there is a string version of the field?

        • 1. Re: "Interesting" Impact of Data Type on Null Value
          ye li

          Update: I just found when there is no string Days to Conversion, there is null value for number Days to Conversion. When there is no string Days to Conversion, null values become 0.... Why?????!!!!!

           

          There is String Days to Conversion:

          Days to Conversion (number)    Count

          null                                                  20

          0                                                     30

          total                                                 50

           

          There is NO String Days to Conversion:

          Days to Conversion (number)    Count

          0                                                      50

          total                                                 50