4 Replies Latest reply on Feb 23, 2016 11:46 PM by Mark Fraser

    My Calculated Field Doesnt Show Negative Numbers

    Sunil Rai

      Hi all,

       

      I have two measures I created. Holiday Sales and Fall Sales.

       

      I subtract them normall

       

      Holiday Sales - Fall Sales

       

      My Fall Sales are all numbers while Holiday is all 0.

       

      Yet when I do up a chart of this, it doesn't show me negative values. Any reason why?

       

      Sunil

        • 1. Re: My Calculated Field Doesnt Show Negative Numbers
          pooja.gandhi

          Hey Sunil!

           

          You got some screenshots? Are you sure holiday sales are 0 or are they null? If it is literally null, the calculation wouldn't operate the subtraction. Either way, you would be better off by giving us some sample data.

           

          Pooja.

          • 2. Re: My Calculated Field Doesnt Show Negative Numbers
            Sunil Rai

            Hey Pooja,

             

            There are nulls that I already converted to 0 using the LOOKUP(ZN(SUM([ Net Amt (USD)])),0)

             

            However, I cant use the newly created calculated measure above to  create measures for Holiday and Fall separately.

            • 3. Re: My Calculated Field Doesnt Show Negative Numbers
              Sunil Rai

              So my initial measure is "Net Amt USD".

              It has several nulls which is essentially 0.

               

              So I used the function LOOKUP(ZN(SUM([ Net Amt (USD)])),0) to replace the nulls with zeros.

               

              Then I tried to pull out just the Holiday Sales (Net Amt USD) using this calculation. IF [Season] = 'Holiday' THEN Net Amt USD (the new calculated field replacing the nulls with zero), but it doesn't work, the calculation shows an error.

               

              However, if I pull out Holiday sales using the INITIAL Net Amt USD measure it works. But when I use the 2 newly created measures (Holiday Sales, Fall Sales) to minus off each other.

               

               

              I try every way but my negatives show as 0.

              • 4. Re: My Calculated Field Doesnt Show Negative Numbers
                Mark Fraser

                Hi Sunil

                 

                Excuse the interruption...

                Is there a reason you are using the LOOKUP() function? Its likely to have been set up wrongly, and therefore is affecting the rest of your calculation.

                Something like this, should be sufficient - ZN(SUM([Net Amt (USD)]))

                 

                Otherwise, if you could mock up some basic numbers in Excel (to replicate the problem), it would be easier to help!

                I can mock up an example, but i dont know if it will help.

                 

                Are you using 9.2.4?

                 

                Cheers

                Mark