Skip to Content
Author's profile photo Former Member

Dealing with Null Values in a Chart

Hello Everyone,

I found lot of queries being asked that Charts not displaying properly when have null values against then in the measure values. So thought of sharing my past experience that I had. below id the simple raw data that we will be using against the Line Chart.

Month Sales
Jan 100
Feb
Mar 250
Apr 50
May 75
Jun 375
Jul
Aug 66

So we can see that we have null values for the Sales in the month of Feb and Jul. Now we will create a simple line chart against the Raw data and you can spot that on below chart we have a break in the line in the month of Feb and Jul which is not what we want.


Null Values.JPG

So I created another measure using sales and added up 0 to it so as to handle the null value.

[Test]=[Sales]+0

The below chart is created with the newly created measure and you can see that now we are getting the chart way we want it.


Non Null Values.JPG

Hope I was able to explain to some extent. feedback and suggestions are always welcome.

Regards

Niraj

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Niraj,

      Nice trick.

      Is it different from the function Interpolation?

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hello Divya,

      To an extent I would say "Yes" because in interpolation an unknown function f(x) for which someone supplies you with its (exact) values at (n + 1) distinct points, but here we are missing those values so explicitly adding up 0 in order to get a linear curve in the Grapgh which would have been missed otherwise.

      Hope I was able to answer.

      Regards

      Niraj

      Author's profile photo Former Member
      Former Member

      Hi Niraj,

      Yeah you are right, there is a difference.

      Thanks,

      Divya

      Author's profile photo Former Member
      Former Member

      HI NIraj/divya,

      I Worked Formula works but As you are talking about interpolation

      May I Know what it is ?

      you mean to handle duplicate Values?

      Thanks,

      Varun

      Author's profile photo Former Member
      Former Member

      http://scn.sap.com/thread/3893236

      HI NIraj/Divya,

      Could you please check Above Post link of mine of loop issue. till now  no one responded

      Waiting for your valuable answers .as you are experts 🙂

      Thanks,

      Varun

      Author's profile photo Former Member
      Former Member

      Hi Varun,

      Please refer http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp5_ffc_user_guide_en.pdf and search for interpolation.

      Basically this is to get the continuous trend line kind of thing in chart or to show missing measure values in report.

      If you have data for Apr and June and if you dont have any data for May but you want have continuous graph to show the trend of measure value from month on month then you will go with this function.

      Example in the above document will give u clear idea

      Author's profile photo Former Member
      Former Member

      Hi,

      sorry to get into this topic, but I just wanted to add that from a user perspective, I don't know if just the interpolation would be good (depends a bit on the data of course).

      In the example, there have been no values for the missing data. The interpolation would generate values, which are not there in the data. In a real world example, the connection between the "real" data and the generated data by the interpolation should be dotted to mark the generated data. I don't know, if that's possible in WebI though.

      Just my 2 cents, regards, Philipp

      Author's profile photo Former Member
      Former Member

      Hi Phillipp,

      You are right, interpolated values will be different from actual values. it depends on the data  and what user wants. This function is mainly used to show linear regression i.e. best fitting line with a given points. 

      Answer to your second question, as per my knowledge there is no option to split a single line to show dotted and continuous line based on interpolated or actual values...

      Hope this answered your question

      Author's profile photo William MARCY
      William MARCY

      Great Webi trick !

      You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

      William

      Author's profile photo Former Member
      Former Member

      Great trick

      Keep Posting

      Cheers!