1 Reply Latest reply on Jun 23, 2016 6:34 AM by Mahfooj Khan

    Issue with nulls

    vishal gogineni

      Hi Everyone ,

       

      I have a problem with nulls in tableau.

       

      I need to compare the current value of a month to the previous year value of that month. For instance, June – 2014 to June - 2013.

      If the value increases I need an upper arrow.

      If the value decreases, I need a downward arrow

      If the value is constant. then a circle (I have done this in shapes easily)

      It works perfectly fine for the above conditions except for nulls

       

      The attached tableau workbook is an example of displaying nothing in may 2013 .

      The value of may 2012 is 3 . The value in may 2013 is null. I want a downward arrow sign as the number of records have decreased.

      I have done Zn function in the calculated fields that I used.

      I want tableau to consider a null as zero and then compare the previous 12th month value and display the appropriate shape.

       

      I have used the following calculated fields :

      For calculating the current and an year ago value.

      IF last()!=FIRST() then

       

      (if

      last()=0 then

      (

      IF (ZN(SUM([Number of Records]))-LOOKUP(ZN(SUM([Number of Records])),-1)) >0

      then 1 elseif ZN(SUM([Number of Records]))-LOOKUP(ZN(SUM([Number of Records])),-1) = 0

      then 0 else -1 end)

      END)

      END

       

      Thank you for your time and effort.

        • 1. Re: Issue with nulls
          Mahfooj Khan

          Is that what you wanted?

          I've created two calc measure One is for previous year month sales and second is current year month sales i.e. your month year selected from parameter. Here you can see I've selected Oct-12.

          Oct-12 sales: 31405

          Oct-11 sales: 31453

          which is less then previous year so its down arrow as per your logic.

          Go through the workbook. Let me know If you've any query.

           

          Mahfooj