Home / Business Intelligence / Two Advantages of a Manual Maximum Calculation in Xcelsius

Two Advantages of a Manual Maximum Calculation in Xcelsius

Problem A: I have data that could be in the 1000s but sometimes could be 1 or 2.  Users do not want to see a division of 1.5 (or any part of a whole) since the data is tracking incidents and there are never “half incidents.”  How do I force the values to integers without losing the divisors for the high numbers (i.e. when there is 1000 maximum, I want to still see 250, 500, 750 divisors)?

 

Solution: Set a manual calculated maximum

{ =IF(MAX(AE15:AE26)<100,100,MAX(AE15:AE26)) } and then set the division number to 1.  With the division number to 1, it often also looks better to remove the grid lines (in case the data for the charts sometimes is much higher).

          

  Problem B:  I want to display a line chart on top of a stacked bar chart because there is no combination chart with this option.  My data for the stacked bar chart, however, is sometimes over 100 and sometimes under 100 and the extra space shifts the chart so that the two do not align.  How can I make the layered charts always align? 

 

 (Layered line chart does not line up with stacked bar chart when maximum is less than 100)

 

 (Layered line chart lines up with stacked bar chart when the maximum is over 100)

 

Note: If the charts are less than a magnitude different, the following method is a not-so-elegant-but-perhaps-acceptable workaround.  In the example above, I have emphasized the issue of alignment by making the different much greater in which case a more-involved display control of multiple charts may be the only acceptable option. 

Solution: Set a manual calculated maximum within an if() statement so that the maximum is always 100 or greater even if the stacked bar chart total is less than 100. (See above note for exception) 

 

This “fix” may not be acceptable for the right visual consumption. What may need to be done when the data is more than a magnitude of 10 different is to work with display properties for two charts with different widths based upon the maximum chart height formula instead.

About Amy

Amy is a Senior Technical Consultant at InfoSol. In one form or another, she has been working with data for 30 years. Amy’s education and early work experience in Analytical Chemistry led to a passion in improving reporting out of Microsoft Excel-based and Access-based tools. Frustrated with the monotony of “cranking out samples,” she studied and trained to get her MCSE with Microsoft in order to follow her IT-focused passions. Amy has been with InfoSol for 10 years now. Certified as a BusinessObjects Certified Professional, she can often be found providing client installations and migrations, as well as universe, report and dashboard design. In addition to BusinessObjects products, she is well-versed in Version Manager and 360View. Recently, Amy has completed migrations to XI3.x for Sohar Aluminium in Oman and XI 4.0 for WAI Global in Florida, but she admits she still has the greatest passion for process improvement in whatever form it may take.

Check Also

IBIS 2017 Dashboard Awards

InfoSol Dashboard Awards Wow Superheroes at IBIS 2017

The superhero theme was a huge hit at this year’s InfoSol Business Intelligence Seminar (IBIS) ...