Simulating Conditional Formatting in Xcelsius

Dec 22nd, 2008
by Srinivas Dandamudi.
1 comment

In this Post Srinivas Dandamudi of BIR Solutions shows us how to simulate Excel conditional formatting in Xcelsius 2008. The primary component of focus here is the spreadsheet component.

Note: Post edited by Kalyan Verma

If you are trying to use the Spreadsheet component in Xcelsius and expecting it to function like a regular Excel spreadsheet, then I’m afraid you are wrong. Unfortunately Xcelsius doesn’t support Conditional formatting for the Spreadsheet component. However here is an alternative to achieve the same.

I used the rectangle component to simulate this functionality. Try the interactive model below and read the steps. I would recommend you to Download the Source Files at the end of this post and go through the steps while looking at the XLF model.

It’s Interactive:

Steps to get it;

  1. Create a sample sales data excel file.
  2. Drag the Spreadsheet table component into the canvas and bind it to the resulted salespersons region values. After binding it to the display data we need to select the ROW and bind the source data and destination to the excel sheet.
  3. Drag and drop a Column Chart onto the Canvas and map the spreadsheet table destination to the Chart as Series. On the Chart Properties window, go to the Alerts tab and check the box which says “Enable Alerts”. Then under “Color Order” Select the radio button which says “High values are good”.conditionalformattingcx1
  4. Then create an Alerts table in your Excel model based on your targets. Check the values by using the formula =IF(G23<30,1,IF(AND(G23>30,G23<70),2,IF(G23>70,3,””))).  The resulted table is as follows.
    conditionalformattingcx2
  5. Carefully place 3 rectangles for each cell on the spreadsheet component. Make sure that they are aligned properly on top of each other. The dimensions of the rectangles should be the same as that of  a single cell on the spreadsheet component. Color the rectangles Red, Green and Yellow and set the dynamic visibility appropriately. Display status:  1 for red, 2 yellow and 3  Green.conditionalformattingcx3
  6. The above image shows the interactive with the table and Chart(Alerts)

Download Source Files:

download

If you have any questions related to the above post, please leave a comment or eMail Srinivas.

Posted in: Tricks.
Tagged:

Webinar Archives: Xcelsius Gurus – Top 10 Excel Tips for Success

Dec 20th, 2008
by Kalyan Verma.
2 comments

Mico Yuk and Ryan Goodman are probably two of the leading Xcelsius Developers in the industry right now. Most of us know Ryan from his blog, company, books and earlier webinars. This is Mico’s first webinar and you will be surprised to know that she is the owner of the famous Xcelsius blog, www.everythingxcelsius.com. Ryan starts off the webinar with 5 essential Excel Tips that an Xcelsius developer should always keep in mind while designing dashboards and Mico takes it over from Tip # 6 and focuses on Component organization and other best practices within the design mode of Xcelsius. Its a must watch for all Xcelsius Developers out there.

Note: This is a direct Excerpt from the Business Objects Website.

Join two Xcelsius Gurus as they share their best practices and techniques for the proper use of Excel with Xcelsius and methods of how to successfully manage your Xcelsius components.  Adopting their top ten list of do’s and don’ts will ensure you can implement standards within your organization for efficient dashboard development, leading to a more scalable, manageable Xcelsius dashboard project.

In this session you will learn how to:

  • Optimize your Xcelsius Excel Sheet for Success
  • Manage your Xcelsius Component List
  • Streamline your Xcelsius projects, making them easy to manage and share

webinarbutton

Posted in: Webinar.
Tagged: ·

Create Basic Calculator using Xcelsius 2008

Dec 12th, 2008
by Kalyan Verma.
2 comments

A basic windows calculator is quite often used for simple calculations. The other day I was trying to calculate my earnings for the year 2008 and my sub-conscious mind  automatically let me open windows calculator to do some basic math. That’s when I thought to recreate it using Xcelsius 2008. It took me 5 minutes to come up with a logic in Excel and then get the components together on Xcelsius Canvas. This is what I came up with:

Its Interactive:

download (Download the source files before reading further)

Components used:

Two “Input Text” components for the Input numbers and them binded them to write data to cells B4 and D4

One “Value” component to display the result and binded it to pick data from cell F4

A label component to show the function (/,X,-, +)

Another label component to show the equal sign (=)

Four Toggle buttons binded to cells H4:L5 to insert the selected function into cell C4

Background and Rectangle components to deck it up

Formulas Used:

I used the HLOOKUP to pick up values based on the function selected.

Fell free to leave a comment.

Posted in: Fun, Other.
Tagged:

Drill Down to More Detailed Charts using Excel Lookups and Xcelsius Chart Drill Down

Dec 1st, 2008
by Jim.
13 comments

In this Post Jim Brogden explains how to link multiple charts to a single drill down using Excel Lookups and Xcelsius 2008 chart drill down feature.

Use the Drill Down feature in combination with Excel logic to pass information from one chart to many others.

Its Interactive


Click here to see the Blown up version.

1. The first step is to either add a Column Chart to an existing Xcelsius model or edit an existing one, set the values and labels to populate the chart then select the Drill Down tab.

2. Check the ‘Enable Drill Down’ box and select Row as the Insertion Type.  The Insertion Types available when enabling the Drill Down feature on a Column Chart are the following: Position, Value, Row, Column, Status List. The Position type returns the position in the order where the selected bar appears in the list. The Value type returns the value of the selected bar.  This example will be using the Row type, which will return the label of the selected bar.

3. After selecting Row as the Insertion Type, point to the Source Data (the same as the charts Labels), and choose a Destination cell.  This is an important cell because it will be used to identify values in the additional charts.  Now that the drill-down selection is in the destination cell (B2 in this example), it will be used by a series of simple HLOOKUP formulas to identify the data associated with it to display in additional charts.

4. Cell K3 receives the dynamic value placed in B2 and will be the new charts’ heading. The cell with the first dynamic value is K4.  This value is obtained through HLOOKUP by using the value in B2 and looking through the “detailed data” for a match.  Once the match is found, the value in the second row is displayed.  Here is the formula:  =HLOOKUP($B$2,$E$3:$I$8,2,FALSE)

Utilize the Excel HLOOKUP formula

Utilize the Excel HLOOKUP formula

5. A second example is used that is very similar to the one above but the value is passed to cell K13 and additional HLOOKUPS are used to identify the correct values associated with K13. These values appear in cells K14:K18 and are used to create the pie chart.

6. The main chart can set a Default Selection (on the Drill Down tab in properties) which will be the subsequent drill-down value picked up by the additional charts.

Drilling Down from Dollars by State

Drilling Down from Dollars by State

Feel free to send me an email if you have any questions.  Thank you.

Posted in: Articles, Charts, Dashboards, Examples, MS Excel, Xcelsius 2008.
Tagged:

First look at Qlikview

Nov 28th, 2008
by David Lai.
7 comments

In this post David Lai, does a brief review on Qlikview, a Visual analysis tool.

Qlikview is a great dashboarding and analysis solution that allows analysts to easily and quickly display data in a meaningful way. I would like to give a technical introduction into some of the benefits I have come across while working with Qlikview.

One thing that I like right off the bat for Qlikview is that it loads very fast. After creating a dashboard, the user has the choice of creating an AJAX version of Qlickview and this pretty much loads instantaneously. Setting up qlikview was quite easy and loading the data through an ODBC connection was easy as well. The manual provides clear instructions on accomplishing this.

Once you have your qlikview layout setup, the main way of making queries is through the selection of field values. When you make a selection, the program instantaneously shows all the field values in the document that are related to the selected field value.

To make a query, or a search, in the database, you just click on something you want to know more about. By making consecutive selections this way, it is thus possible to step by step
get closer to the answer you are looking for.


For example, in Fig 1 I wanted to select information from 1998 and Canada. All I had to do was click on 1998 from the Year listbox and U.S.A from the Country list box. I am now able to see which customers I am able to further filter on and which salesman if desired. In addition based on my filter selections, I have a chart that shows the total sales in 1998 for U.S.A.


Qlikview also has some user intuitive features such as objects to display the user’s current selection and the ability to display all aggregates for a certain measure (ie: sales)


To simplify searches for users, qlikview provides an easy to use text and numeric search option that is easily accessible from clicking on the top of a listbox. In addition, some dashboards may be very complex so users have the option of bookmarking their selection. For example I can bookmark the country, year and salesperson that I have selected and access it next time. Finally if I were to make a mistake on the current selection, I can easily revert back to the previous selection since there is a history of 100 selections.

Moving from tab to tab will also save your selection so you won’t need to select everything all over again.


Another neat feature that qlikview has is the ability to filter the items to be displayed on your chart. For example if I wanted a chart that only shows China, USA, Brazil, Australia, and India, I could just easily perform my drag selection on the chart directly.


Furthermore, Users can easily go into the chart properties and change it to another chart type or even a pivot table.

These are just a few of the powerful things that Qlikview can accomplish with very little effort. I’ll be writing more on the advanced features of Qlikview on my next article so stay tuned.

Posted in: Dashboards, Qlikview, Visual Data Analysis.
Tagged: ·

Page 30 of 41« First...1020...2829303132...40...Last »