4 Replies Latest reply on Aug 16, 2018 2:19 AM by Shariq Mohd

    Replacing null data point with 0 in area chart

    Shariq Mohd

      Hi Folks,

       

      I created this graph which looks bits awkward because it has missing data point (thats how the data is, imagine this is a segment in your company in which none of the order are returned by the customer) due to which the area graph doesnt connect with each other . I am looking for a way which can help me visualize this trend better for e.g. when there are no return orders then instead of null (blank) it should return or show 0 so that the data point connects with each other. Unfortunaltely i cannot share the data.

       

       

      Thanks

        • 1. Re: Replacing null data point with 0 in area chart
          Simon Runc

          Hi Shariq,

           

          There are a few ways to go here....where I'm assuming your Date Field is a real date.

           

          Firstly you can tick this option....

           

          Dates (and Bins) are "range aware" pills (As they know their Min, Max and Increment), so this pseudo-fills the missing values, so we can access them.

           

          Once we have this you can right click and select format on your measure, and change the pane option to this....

           

           

          OR you could create a formula like this (and just put in on the canvas in place of your measure)

           

          IF ISNULL(SUM([Sales])) THEN 0

          ELSE SUM([Sales]) END

           

          Hope that helps.

          • 2. Re: Replacing null data point with 0 in area chart
            Shariq Mohd

            Hi Simon,

             

            Thank you very much for looking into this and this did take me somewhere however the "MM-YY" you see there its not a real date its a string derived from a "date dimension" and mixed with string function. see below.

             

            Quarter

            (

            'Q'+RIGHT(STR(DATENAME('quarter',[Latest Closure Request Date])),2)+'-'+

            RIGHT(DATENAME('year',[Latest Closure Request Date]),2)

            )

            Month

            (

            LEFT(DATENAME('month',[Latest Closure Request Date]),3)+ '-'

            +RIGHT(Str(DATEPART('year' ,[Latest Closure Request Date])),2)

            )

             

            these are 2 calc which is used in a parameter and the user value are "monthly" and "quarter" user and choose either of them to choose the type of trend they would like to see.

            • 3. Re: Replacing null data point with 0 in area chart
              Simon Runc

              hi Shariq,

               

              So actually you don't need the 'Show Missing Values' bit (...I just assumed we needed that, to get the extra/missing marks, but apparently not!)

               

              In which case your string versions should just work, with the NULL as 0, and "Show at Default Values"

               

               

              Let me know if that doesn't do the trick for you.

              • 4. Re: Replacing null data point with 0 in area chart
                Shariq Mohd

                Hi Simon,

                 

                Thanks it worked .