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.
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.
Hope I was able to explain to some extent. feedback and suggestions are always welcome.
Regards
Niraj
Hi Niraj,
Nice trick.
Is it different from the function Interpolation?
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
Hi Niraj,
Yeah you are right, there is a difference.
Thanks,
Divya
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
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
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
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
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
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
William
Great trick
Keep Posting
Cheers!