{"id":1876,"date":"2012-09-24T15:30:44","date_gmt":"2012-09-24T13:30:44","guid":{"rendered":"http:\/\/www.hackingsap.com\/blog\/?p=1876"},"modified":"2012-09-23T09:33:49","modified_gmt":"2012-09-23T07:33:49","slug":"sap-businessobjects-dashboards-variable-maximum-limits-in-charts-xcelsius","status":"publish","type":"post","link":"https:\/\/www.hackingsap.com\/blog\/sap-businessobjects-dashboards-variable-maximum-limits-in-charts-xcelsius","title":{"rendered":"SAP BusinessObjects Dashboards: Variable maximum limits in charts"},"content":{"rendered":"

If you use a chart in\u00a0SAP BusinessObjects Dashboards (Xcelsius) you have the option to adjust the scale of the y-axis<\/strong>. By default Dashboards uses the automatic axis and makes up its own minimum and maximum limits. The biggest drawback of this feature is that it won’t let the y-axis start at zero<\/strong> all the time; this depends on the data set the chart is displaying. To make honest comparisons of data I believe a chart should show the complete picture and start its value axis at zero.<\/p>\n

The example below shows the same numbers in two bar charts with different limits on the y-axis. Do you see what is happening here? The left chart might give you the impression of a giant decline in market share; losing more than half it. In reality, market share is down from 60% to 56%, which is a decline of only 6,67%.<\/p>\n

\"\"<\/p>\n

In Dashboards I always use the option to manually<\/strong> set the limits of the y-axis. But, what if you don’t know what value ranges have to be shown in the dashboard? And what if want to display multiple different sets of data in the same chart? Or you have created an option in your dashboard to filter the data and want to show some drilled subsets of the data? If you set the maximum limit too low there is a risk some values won’t be shown on the chart. If you set the maximum limit too high a data set with smaller numbers won’t show any differences between the values anymore.<\/p>\n

\"\"<\/p>\n

So we have to make the maximum limit variable<\/strong> to tackle this problem. We are going to use some MS Excel formulas to do this. Make sure you check the example at the end of this post.<\/p>\n

Let’s say we’ve got a data set with the monthly results for some products. First we set up the chart and the data insertion with a selector component so we can choose which product should be shown.<\/p>\n

Next we have to calculate the maximum value of the row of data with the MAX<\/strong> function: =MAX(number1, number2…)<\/strong>.<\/p>\n

But, we actually want to use nice rounded numbers as our maximum limit. There is an Excel formula called CEILING<\/strong> to do this: =CEILING(number, significance)<\/strong>. The significance part is interesting. If we don’t put something in there the number will be rounded up to the nearest integer (10.3 turns into 11). That’s nice, but we actually want to round up to tens, hundreds, thousands<\/strong> ands so on. That’s where the significance part comes into play. For example, to round up to hundreds you should put 100 here, so 35 gets rounded up to 100 and 102 gets rounded up to 200.<\/p>\n

How can we determine which significance value we should use? We are going to use some good old\u00a0exponentiation<\/a> for this: 102<\/sup> = 100 (10 to the power 2), 103<\/sup> \u00a0= 1000 (10 to the power 3) and so on. If the maximum value of a row of data is 535585, we want the maximum limit to be 600000. The significance value should be 100000 in that case, which is 105<\/sup> (10 to the power 5). The number of characters<\/strong>in 535585 is 6. But, if we use this 6 as the power we would get 1000000 as the nearest round up value. This is 10 times too big. So we need to subtract 1 from the power (10(6-1)<\/sup>) to get the significance value we want!<\/p>\n

To count the number of characters we can use the LEN<\/strong> function: =LEN(number)<\/strong>. To calculate the significance value this Excel formula can be used: =10^(NumberOfCharacters-1)<\/strong>.<\/p>\n

To finish things of we\u00a0can even make the number of divisions<\/strong> variable now. Just divide the maximum limit through the significance. This prevents us from getting a ‘difficult’ set of axis labels. For example, if we always show 5 divisions, and our maximum value is 90, we would get 18, 36, 54, 72, 90 as labels.<\/p>\n

Check the example below to see what happens when selecting the several rows of data.[kml_flashembed publishmethod=”static” fversion=”8.0.0″ movie=”http:\/\/hackingsap.com\/blog\/wp-content\/uploads\/Max_Limit_example.swf” width=”650″ height=”500″ targetclass=”flashmovie”]<\/p>\n

\"Get<\/a><\/p>\n

[\/kml_flashembed]<\/p>\n","protected":false},"excerpt":{"rendered":"

If you use a chart in SAP BusinessObjects Dashboards (Xcelsius) you have the option to adjust the scale of the y-axis. By default Dashboards uses the automatic axis and makes up its own minimum and maximum limits. The biggest drawback of this feature is that it won’t let the y-axis start at zero all the time; this depends on the data set the chart is displaying. To make honest comparisons of data I believe a chart should show the complete picture and start its value axis at zero.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[44,126,6],"tags":[19,124,285],"class_list":["post-1876","post","type-post","status-publish","format-standard","hentry","category-knowledge-sharing","category-sapbusinessobjects-dashboards","category-xcelsius","tag-excel","tag-sap-businessobjects-dashboards","tag-xcelsius"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pKaa2-ug","jetpack-related-posts":[{"id":1181,"url":"https:\/\/www.hackingsap.com\/blog\/sap-businessobjects-dashboards-4-0-cookbook-released-xcelsius","url_meta":{"origin":1876,"position":0},"title":"SAP BusinessObjects Dashboards 4.0 Cookbook released","author":"Xavier Hacking","date":"May 26, 2011","format":false,"excerpt":"I am very happy to announce the release of the SAP BusinessObjects Dashboards 4.0 Cookbook I worked on the past year with co-author David Lai and the team at Packt Publishing. In this book we discuss over 90 so-called recipes that give a step-by-step explanation of the possibilities of SAP\u2026","rel":"","context":"In "Books"","block_context":{"text":"Books","link":"https:\/\/www.hackingsap.com\/blog\/category\/books"},"img":{"alt_text":"SAP BusinessObjects Dashboards cookbook","src":"https:\/\/i0.wp.com\/www.hackingsap.com\/blog\/wp-content\/uploads\/sbodcookbook-243x300.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1127,"url":"https:\/\/www.hackingsap.com\/blog\/bi4-product-names-xcelsius-dashboards","url_meta":{"origin":1876,"position":1},"title":"BI4 product names for Xcelsius","author":"Xavier Hacking","date":"March 21, 2011","format":false,"excerpt":"Overview of the new BI4 product naming for SAP BusinessObjects Dashboards (fka Xcelsius).","rel":"","context":"In "SAP BusinessObjects Dashboards"","block_context":{"text":"SAP BusinessObjects Dashboards","link":"https:\/\/www.hackingsap.com\/blog\/category\/sapbusinessobjects-dashboards"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2077,"url":"https:\/\/www.hackingsap.com\/blog\/article-for-businessobjects-expert-dashboards-add-ons-xcelsius","url_meta":{"origin":1876,"position":2},"title":"Article for BusinessObjects Expert on add-ons for Dashboards","author":"Xavier Hacking","date":"January 13, 2013","format":false,"excerpt":"I wrote an article for the BusinessObjects Expert platform about add-ons for\u00a0SAP BusinessObjects Dashboards. These add-ons are third-party\u00a0plugins that extend the functionality of the standard toolset of SAP BusinessObjects Dashboards\u00a0and they can consist of additional components or connectivity options. Examples are add-ons that let you load data from CSV-files or\u2026","rel":"","context":"In "SAP BusinessObjects Dashboards"","block_context":{"text":"SAP BusinessObjects Dashboards","link":"https:\/\/www.hackingsap.com\/blog\/category\/sapbusinessobjects-dashboards"},"img":{"alt_text":"boexpert","src":"https:\/\/i0.wp.com\/www.hackingsap.com\/blog\/wp-content\/uploads\/boexpert.jpeg?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.hackingsap.com\/blog\/wp-content\/uploads\/boexpert.jpeg?resize=350%2C200 1x, https:\/\/i0.wp.com\/www.hackingsap.com\/blog\/wp-content\/uploads\/boexpert.jpeg?resize=525%2C300 1.5x"},"classes":[]},{"id":2768,"url":"https:\/\/www.hackingsap.com\/blog\/sap-businessobjects-dashboards-4-1-cookbook-2nd-edition-coming-this-spring-xcelsius-cookbook","url_meta":{"origin":1876,"position":3},"title":"SAP BusinessObjects Dashboards 4.1 Cookbook (2nd edition) coming this spring","author":"Xavier Hacking","date":"March 5, 2015","format":false,"excerpt":"We are currently in the very final stages of editing the second edition of the\u00a0SAP BusinessObjects Dashboards 4.1 Cookbook. We expect the book to\u00a0be available in an eBook and paper edition in just a few weeks from now (April 2015). For this updated version of the cookbook we broadened the\u2026","rel":"","context":"In "Books"","block_context":{"text":"Books","link":"https:\/\/www.hackingsap.com\/blog\/category\/books"},"img":{"alt_text":"coming soon mockup_EN_2","src":"https:\/\/i0.wp.com\/www.hackingsap.com\/blog\/wp-content\/uploads\/coming-soon-mockup_EN_2-73x90.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":2849,"url":"https:\/\/www.hackingsap.com\/blog\/sap-businessobjects-dashboards-4-1-cookbook-sample-chapter-free-pdf-download","url_meta":{"origin":1876,"position":4},"title":"SAP BusinessObjects Dashboards 4.1 Cookbook Sample Chapter","author":"Xavier Hacking","date":"April 8, 2015","format":false,"excerpt":"We uploaded a free sample of the\u00a0SAP BusinessObjects Dashboards 4.1 Cookbook containing the first chapter\u00a0of the book. It will give you a good idea of how the step-by-step cookbook\/recipe structure, supported with lots of screenshots works and looks like. Check it\u00a0at PacktPub.com\u00a0or just browse through the Slideshare\u00a0below.","rel":"","context":"In "Cookbook"","block_context":{"text":"Cookbook","link":"https:\/\/www.hackingsap.com\/blog\/category\/cookbook"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":444,"url":"https:\/\/www.hackingsap.com\/blog\/more-htg-from-sap-setting-up-authorization-for-xcelsius-in-sap-bw","url_meta":{"origin":1876,"position":5},"title":"More HTG from SAP: Setting up authorization for Xcelsius in SAP BW","author":"Xavier Hacking","date":"March 12, 2010","format":false,"excerpt":"SAP HTG on creating authorization objects\/roles in SAP BW for Xcelsius dashboards.","rel":"","context":"In "Knowledge sharing"","block_context":{"text":"Knowledge sharing","link":"https:\/\/www.hackingsap.com\/blog\/category\/knowledge-sharing"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.hackingsap.com\/blog\/wp-json\/wp\/v2\/posts\/1876","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hackingsap.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hackingsap.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hackingsap.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hackingsap.com\/blog\/wp-json\/wp\/v2\/comments?post=1876"}],"version-history":[{"count":16,"href":"https:\/\/www.hackingsap.com\/blog\/wp-json\/wp\/v2\/posts\/1876\/revisions"}],"predecessor-version":[{"id":1891,"href":"https:\/\/www.hackingsap.com\/blog\/wp-json\/wp\/v2\/posts\/1876\/revisions\/1891"}],"wp:attachment":[{"href":"https:\/\/www.hackingsap.com\/blog\/wp-json\/wp\/v2\/media?parent=1876"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hackingsap.com\/blog\/wp-json\/wp\/v2\/categories?post=1876"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hackingsap.com\/blog\/wp-json\/wp\/v2\/tags?post=1876"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}