Go to Top

Capitalize Analytics Blog

Upcoming Webinar – ThoughtSpot: Stop building reports/dashboards and just “search” for answers!

Thursday, April 26th at 12:00 pm CT

Capitalize Analytics works with dozens of vendors to solve data challenges. Rarely are we excited or blown away by a technology. ThoughtSpot will be the answer to a lot of our clients’ data/reporting challenges.

Tableau, Qlik, Cognos, BusinessObjects, etc. are all great tools in the correct hands. The problem is that even the simplest tools are too complex for average users. ThoughtSpot changes this by allowing end users to “search” for answers just like they would in Google, Amazon, etc.

With ThoughtSpot you just search for “Sales merchant product weekly last 3 months” and immediately get the answer!

Gartner has recognized ThoughtSpot as an analytics visionary and the biggest mover on the Magic Quadrant. 

 

ThoughtSpot doesn’t replace your traditional BI strategy. Instead, it gives a new, non-technical audience the ability to truly “self-serve” for the first time.

 

Join Capitalize Analytics  to see how ThoughtSpot will have your company “searching” for answers more quickly than any other tool available!

 

Can’t make it? Still register and we’ll send you the recording!

 

Register

INDEX and MATCH Functions – Multiple Criteria VLOOKUPs

In an ideal world, we would always receive the data we need in exactly the format we need to have it in. However, in the real world, analysts are often given dumps of data from various sources that need to be consolidated into a useful report. Many times, this consolidation is done in Excel using the VLOOKUP function. A limit of the VLOOKUP function, however, is that it can only be used to join data on a single criterion. A less commonly used way to join the data together is through a combination of the INDEX AND MATCH functions. The INDEX/MATCH method can be used when we need to join the data on single or multiple criteria, making it more flexible than the VLOOKUP function.

Following is a case where we need to join data on multiple criteria. Suppose our company is a Natural Gas producer and uses its budgeted Ethane (C2) and Propane (C3) volumes to hedge against price fluctuations. We are asked to build a report showing the Actuals-to-Budget variance of our sales volumes by meter by month for our six (6) highest volume meters. We have built the structure of our report and updated the Budgeted volumes as well as the formulas to calculate the variances. All that we need to complete the report are the Actual volumes.

 Actuals to Budget Variance Report:

Actuals Data:

For the Actuals Data, we receive a dump of all NGL sales for all the company’s meters and all months.

To bring the Actuals data accurately into our report, we need to join the data on not only the Meter #, but also on the Product and the Month. A VLOOKUP would be able to join the data on any one of these criteria, but not all three. To join on all three criteria, we can use the Index/Match option.

First, let’s break down the Index function and Match functions separately.

 INDEX Function (array, row_num, [column_num]):

With the INDEX Function, we specify a range of data and a row and column number within that range (where the upper left cell of the range is in row 1 and column 1). The INDEX function will return the cell where the specified row and column numbers intersect.  For example, below we have set our range to be A1:E541 which is the entire set of our data dump. We then specify it to return the value in Row 3, Column 5. This value is equal to 530.

The INDEX function gives us a way to return an individual cell from a range. To make this function more valuable though, we need a way to find a specific row of data based on the data required in our report. To accomplish this, we can use the MATCH function.

 MATCH Function (lookup_value, lookup_array, [match_type]}:

The MATCH function allows us to enter the value (lookup_value) we want to join and the range (lookup_array) where we want to find that value. For example, below we told the MATCH function to find Meter # 12354 (lookup_value) within range A1:A541 (lookup_array). The function returns number 3 which is the row within the range where Meter # 12354 is first encountered.

Rather than returning the row number with the first occurrence of Meter #12354, suppose we want to return the row for Meter #12354 with product C3 in Production Date 03/01/2017. In other words, we need to return the row number base on multiple criteria.

We can accomplish this by using an Array formula. Specifically, we can use the “&” symbol to join multiple lookup_values and multiple lookup_arrays. One important caveat is that any time you create or edit an Array formula, you must type Ctlr+Shift+Enter in the formula bar to evaluate it. After typing Ctrl+Shift+Enter, curly brackets will appear on the outside of your formula. If you only type Enter after editing an Array formula, a #VALUE! Error will be returned.

Below is a screenshot showing the use of an Array Match formula to identify the desired row based on Multiple Criteria. Note the curly brackets on the outside indicating that it is an Array formula.

The formula returned row number 138. When we look, we can see that row 138 does in fact match all 3 of our criteria: Meter #=12354, Production Date=3/1/2017, and Product=C3.

Populating Actuals in our Report (INDEX/MATCH combination):

Now that we have a way to return a value from a row/column intersection (INDEX) and a way to identify specific rows of data (MATCH), we can combine the two functions to bring the Actuals data accurately into our report. Since our goal is to return the data within certain cells, we start with the INDEX function and specify the column array where the data we want to return is located. In this case, the data we want to return is Mbbls (thousands of barrels), and these values are in range AA4:AA544.

Next, we need to specify the row number for the INDEX function. Instead of specifying a specific number, we can nest an Array MATCH function inside the INDEX function to identify the row number based on our 3 criteria: Meter #, Product, and Month.

We use a combination of Relative and Absolute references ($ symbol) to make sure our formulas will reference the correct cells once we copy them.

Since we only have one column in the Array for our INDEX function, specifying the column number is optional. Here we will explicitly specify it equal to number 1.

We have entered all the arguments for the INDEX function, so we close the final parentheses and press Ctrl+Shift+Enter to evaluate the Array formula. We know we did this correctly because the curly braces appear around our formula.

The formula returned 2,422 MBBLs of C2 for Meter #12347 in January. Searching through our raw data, we can verify that the formula did return the desired value.

We can now simply copy our formulas through the rest of the Actuals columns, and our Actuals-to-Budget Variance report will be complete!

We see that through March, we have exceeded our C2 budgeted volumes on our highest volume meters, but we have fallen short on C3 volumes. The largest drivers of these variances are Meter 3 and Meter 6.

In conclusion, the major advantage of the INDEX/MATCH method is the ability to lookup values on multiple criteria. Using this method can be very helpful for quickly bringing raw data into monthly or ad hoc reports that have been built in Excel.

For additional information, please contact us at marketing@capitalizeconsulting.com!

Cognos Web Services: Direct Access to Report Results

Cognos Web Services (CWS) allows report authors to share a URL that provides direct access to report results. Traditionally, generating report results requires a user to log in to Cognos Connection, navigate through the UI to the desired report, and select prompts and run options each time new results are needed. Utilizing CWS for a report streamlines the user experience to using a URL and getting up-to-date results without the user knowing or accessing Cognos.

CWS URLs can be modified to perform several different tasks including:

  • specifying filters for a report
  • accessing previous versions of a report
  • changing the output data format
  • selecting a specific subsection of the report to display

Because every parameter required to get reporting results is contained within a single URL, it is easier to interface Cognos reports with other software options like Alteryx and Pentaho. Modifying a report for use with CWS is simple and many existing reports won’t require any modification.

Implementation Example:
A company needs to automate a verification process that compares the reports of two different data software packages. They decide to outsource this process to a contractor and would like to share to-the-minute report results without the contractor needing access to the Cognos environment. Using CWS, the contractor only needs the connection address and the relevant report IDs to filter and download data for the comparison.

URL Format:
http://webservername:portnumber/ibmcognos/cgi-bin/cognos.cgi/rds/resource_type/source_type/source_id?option1=val1&option2=val2…

  • webservername:portnumber: Local Cognos Connection Address
  • resource_type: Type of result desired, e.g. outputFormats, promptDescriptions
  • source_type: Specifies source such as path or report
  • source_id: Report-specific unique identifier

Once the source is specified, the user can alter the output with parameters like:


User credentials can be saved in-browser, specified for each URL, using a credentials prompt option.

Examples:
http://test.server/ibmcognos/cgi-bin/cognos.cgi/rds/outputFormat/report/
i4FAD7993E84645DEA0B8C2C555E5E5EF/CSV&selection=UPlist&p_usage=sale


http://test.server/ibmcognos/cgi-bin/cognos.cgi/rds/outputFormat/report/
i4FAD7993E84645DEA0B8C2C555E5E5EF/XML&p_product=res

For additional information, please contact us at marketing@capitalizeconsulting.com!

 

Contact us to join our Blog!

Powerful Date and Time Functions in Excel

Another installment of our educational blog series is here! If you have suggestions for future blog topics, we want to hear them. Email us at marketing@capitalizeconsulting.com with your ideas!

Excel has numerous date and time functions that are very powerful in manipulating and deriving dates and times. In these examples, we are going to use one of them – WEEKDAY – as well as supporting numerical functions – FLOOR and TEXT.

In this first example, I have a goal of walking an average of 10,000 steps a day for the next 10 weeks, and would like to track my progress via a scorecard. I have a spreadsheet where I will log my steps daily and need to report weekly whether my average step count meets my goal of 10,000 steps.

I have built a simple data tracker to track my daily steps:

My next step is to be able to report weekly to see if I am meeting my goal. To do so, I will use the date function DAYS, along with supporting function FLOOR, in order to derive the week number (1, 2, 3, … 10). I want to determine what week each date falls into, relative to my start date of 9/10/2017. Looking at the data above, 9/10 – 9/16 would fall into Week 1, 9/17 – 9/23 into Week 2, and so on, but I want Excel to determine the week number for me.

I start by determining the number of days that have elapsed since the start of my goal, using the DAYS function. The DAYS function takes two arguments – end date and start date – and returns the number of days between those two dates. For end date, I pass the date cell for that row, and for start date, I pass an absolute reference of the start date of my goal (9/10/2017) in this case. As shown in the below screenshot, I have now calculated the Days from the Start using the formula “=DAYS(A2,$A$2)”, entered once, and dragged to the other cells:

Now that I have Days from the Start, I can derive the week that each date falls in using the FLOOR function. In our example, those dates less than 7 days from the start are in Week 1, greater than 7 but less than 14 days are in Week 2, and so on. In other words, if we ignore the remainder, always rounding down, then the formula of (Days from the Start / 7 + 1) would give you the Week. The FLOOR function does just that. It takes whatever number is given and rounds that down to the nearest specified multiple. FLOOR takes two arguments – number and multiple. The function then rounds the number down to the nearest multiple passed.

See below for a few examples of the result FLOOR would output, given the inputs of number and multiple specified:

In our case, we need to derive the week from the given date, knowing we have a start date of 9/10. We can do that using FLOOR, one of two ways.

  1. “=FLOOR(D2/7,1)+1” – Take the number of days and divide by 7 to derive the fraction of week “completed,” adding 1 so that we start with week 1 rather than week 0.
  2. “=FLOOR(D2,7)/7+1” – Take the number of days and round to the nearest multiple of 7 (0, 7, 14, etc.). Divide that result by 7 to convert to Week, and again add 1 so that we start with week 1 rather than week 2.

Either formula will produce the same result, shown below:

Armed with the derived Week, we can now create a quick Pivot Table showing me the average steps per week:

Now that I have my average steps by week, I would like to dive in deeper to see if there are any trends in my walking pattern by day of the week. For example, do I walk more on the weekends when I am not as busy with work?

To glean any patterns associated with day of the week, I first need to determine each day of the week. To do so, I can use the WEEKDAY function. The WEEKDAY function returns the day of the week, in numeric value, of a date passed. The function has two parameters – the date passed, which is required, and an optional parameter called “return type” which determines what days the numbers 1 through 7 correspond to. By default, a return type of 1 is used, meaning a value of 1 is Sunday and a value of 7 is Saturday. If you want to use the default value, you do not need to enter an argument for return type. See below for the full list of return types:

Excel prompts you for return type when entering a new formula, so you won’t need to memorize the return types associated with each configuration:

In my case, the default return type was all I needed, so my formula read, “=WEEKDAY(A2)”, with results shown below:

While I now have what I need to report steps by day of the week, I wanted my report to actually show day of the week, rather than a number. To do so, I used the TEXT function. The TEXT function is used to convert a numeric or date value into text, based on certain criteria. The function takes two arguments – the value to be converted, and a format text parameter which defines the formatting criteria to be used. The format text parameter, in the context of date, has the following definitions:

Using the TEXT function, I am able to derive the day of the week with the following formula: “=TEXT(B2,”dddd”)”. Or, if we wanted to derive in one step, we could via the formula: “=TEXT(WEEKDAY(A2),”dddd”)”. Final data for use in our analysis is:

Now that we have our steps by day of the week, we can move forward with analyzing our daily patterns. There are many examples to build, but I have chosen two.

  1. A pivot table that shows my average weekly step count, with ability to drill down to see steps by day of the week:
  2. A chart that shows, by day, steps by weekFor additional information, please contact us at marketing@capitalizeconsulting.com!

Upcoming Live Webinar! Five Cognos Tips Ever Banner Client Needs to Know

Five Cognos Tips Ever Banner Client Needs to Know

Tuesday, May 15th @ 12 pm CT

Does your institution use Banner? Do you have a love/hate relationship with Cognos? If you answered yes to these questions, you are not alone and this is the webinar for you!

Capitalize Analytics is here to help you get the most out of Cognos. In this webinar we will show you five Cognos tips every Banner client needs to know!

Attend our webinar and let us show you how to you can take full advantage of Cognos for Banner!

Register

Can’t make it? Still register and we’ll send you the recording!

Contact us to join our blog!

Event Studio: On-going events with data change example

Another installment of our educational blog series is here! If you have suggestions for future blog topics, we want to hear them. Email us at marketing@capitalizeconsulting.com with your ideas!

Event Studio: On-going events with data change example

Event Studio can be used to catch changes in data values that prompt further action by your organization. For instance, last name changes require updates to email addresses. If your system doesn’t currently track these changes, you can use the steps below to generate notifications.

This example demonstrates capturing all values of the ‘Last Name’ field on the first run, then sending an email whenever the value changes for an employee.

This condition captures all records because the system requires the field to be completed for every employee entered.

When a change is detected, the following email message is generated and sent. More than one recipient can receive this notification. Separate the email addresses with semi-colons.

The changes are detected for unique values of the ‘Employee Number’ field (every individual.)

Since you want to be notified when a last name changes, you will check for on-going events. Every time Event Studio runs, it compares the current records for ‘last name is not null’ to the records generated from the previous run (the event list). If the employee number was in the previous list and the last name value changed, the email will be sent.

For additional information, please contact us at marketing@capitalizeconsulting.com!

On Demand (Recorded) Webinar: Data Analytics in Healthcare: From Self-Service Analytics to Predicting Patient Outcomes

Recorded, Wednesday, January 31 @ 12pm CT

Healthcare institutions have been collecting clinical and non-clinical data for decades. Data has become a strategic resource that is notoriously underutilized. It is time to release the power of this data! Data-driven decisions enable healthcare decision makers to improve patient outcomes, patient satisfaction, and other VBC-based measures.

In this interactive webinar with Capitalize Analytics and IBM we will:

  • Explain how IBM Analytic solutions can help you analyze and visualize the massive amounts of data collected by healthcare institutions.
  • Demo these technology solutions to show examples of how it may be used at your institution.
  • Share the methodologies we employ to help healthcare institutions become more data-driven.

Please listen to our webinar and let us show you how to LIBERATE YOUR DATA!

Watch Now

Alteryx Key Components

Another installment of our educational blog series is here! If you have suggestions for future blog topics, we want to hear them. Email us at marketing@capitalizeconsulting.com with your ideas!

Alteryx Key Components

We recently had the opportunity to provide an Alteryx demo to a large oil & gas company. The demo was focused on the ease of use of Alteryx while also showing many of the key components of the software.  This was coupled with an industry-specific example and a demonstration of predictive analytics.

In this first Alteryx example, we are looking at two sets of data – one that contains well information with production values, and another that contains well location. We will create a workflow that compares wells with their neighboring infill wells and provides us with specific details on each.

Input and Preparation of Data: An Excel file is brought into Alteryx using the data import tool, then a Select tool is added to determine the correct data types of the file. Next, a Data Cleanse tool is used to remove any nulls and prepare the data for the next steps.

Creation of running-total and Start Dates: The prepared data now takes two paths before eventually being joined back together using a join tool. The first path (top) sorts the data by API number and production date, then uses a Running-Total tool to create a new column, tracking the well output over time. The second path (bottom) uses a Summarize tool to find the minimum production date for each well. The data is then rejoined.

Filtering for date range and creating cumulative totals: The next step involves filtering the current set of data to only contain records within 6 months of the well start date. This displays the built-in functions of Alteryx. The data is then put through a Summarization tool again to pull the maximum value, thereby providing us with the 6-month cumulative amounts.

Joining of well location data: Next, the file containing the well information is introduced to the workflow. This is joined to the existing workflow by the well API number. This allows the demonstration of multiple data types being used together.

Addition of geo-spatial tools: The newly joined latitude and longitude information will be used to create a map. The first tool used is a Create Points tool, which grabs the coordinates and plots them relative to one another. The next tool, Find Nearest tool, allows the points to be related to the nearest four wells within a kilometer. This data will now contain individual data and universal data that can again be summarized.

Categorize the results, create a production delta, and output results: The next step uses another formula to bin the results into a well type category (infill or parent), based on the start data versus the universal start date. The Formula tool is also used to create a field that calculates the difference between a well’s values and the universal values. At this point, the data can be summarized again to see the difference of average performance between infill and parent wells. The data can also be output at this time to just about any file format, and distributed via your preferred reporting and Business Intelligence platform.

At this point, the output data was shown as a visualization that captivates the audience.

The next examples are focused on the predictive analytics capabilities of Alteryx. The active report tabs were also demonstrated live.

Linear Regression: This example used an Excel sheet of actual prices and a series of four indices. The Linear Regression tool is used to show how well each index did in predicting the actual price. The Report and Interactive tabs were explored.

K-Means Clustering: This example used a series of contracts with flags determining their attributes. The tool was used to create clusters of contracts by similar attributes, as well as ranking these clusters by profitability.

Prompting Best Practices: To require or not to require, that is the question.

The 2nd installment of our educational blog series is here!
If you have suggestions for future blog topics, we want to hear them!
Email us at marketing@capitalizeconsulting.com with your ideas!

Updating reports can have its challenges. Sometimes, everything goes according to plan; other times, you are left scratching your head. We recently came across an interesting error while updating a report from a Cognos 10.2 to Cognos Analytics (11) related to prompting and whether a prompt is treated as required or optional.

First, a little background: In any version of Cognos, it is important to understand how prompting works across the report. Several pieces play key roles:

  • Prompt object: The physical object placed on the page, to allow users to make a selection
  • Parameter: The variable or placeholder that stores the selected value
  • Parameterized Filter: The filter that puts the stored parameter to use
    • Can be set to “single-select” or “multi-select” (using =/in)
    • Usage can be set to “required”, “optional”, or “disabled”

The optional usage of the filter allows for a prompt where a user can make a selection, or simply pass by. If the prompt is not used, the filter will be ignored. This has the same effect as selecting all values. One benefit to this method is that a user has fewer clicks on the prompt page, as there is no need to ‘select all’ on each prompt as they navigate. Another, often overlooked advantage is that the background SQL that is generated by the report is actually shorter and more efficient if the prompt is set up as “optional” instead of “select all”.

The setup of an optional prompt in Cognos 10 requires that the parameterized filter associated with a prompt be optional and that the prompt object itself has the property ‘Required’ set to ‘No’. If the filter is required, the prompt object still requires a user to provide a value, even if the prompt object itself is not set to be required. This works conversely as well, meaning that the prompt and the prompt object need to be in sync in order for the “optional” ability to be available.

A Properties pane for a parameterized filter in Cognos 10 (set to “Optional”):

A Properties pane for a prompt object in Cognos 10 (set to “Required: No”):

Note: if the filter is set to “Required,” even with the prompt object set to “Required: No”, the prompt page still requires a value:

The orange asterisk (adornment) denotes a required field:


Here is where the issue arises. In Cognos Analytics (11), a prompt page will allow a user to pass over the prompt object if it is not set to required, even if the associated filter is required. This will result in a text child error for the report. The error can be especially difficult to address, as there is little information provided by the error message, and the report’s individual queries will all work, producing no errors.

Making the issue even more elusive, the prompt page requires the prompts to be answered if run from an open Report Studio instance. This means that only when the report is run from Cognos Connection, will the prompts set to ‘Required: No’ incorrectly allow you to pass no values into a required filter.

A detail filter pain in Cognos Analytics (11) where the filters are set to “Required”:

A prompt object in Cognos Analytics (11) where the Required property is set to “No”:

Results if run from Report Studio in Cognos Analytics. Note the required adornment:

Here is the same report run from Cognos Connection. Note that there is no required adornment, and a user can advance without making a selection, even with the filter set to “Required”:

This is the resulting error:

The solution: A user must keep their filters and prompt objects in sync; either they are both optional, or they are both required. A user cannot rely on the redundancy failsafe that was in Cognos 10. It’s a simple idea once understood, but quite elusive for troubleshooting. Let’s put this in our back pocket and continue to expand our library of tips and tricks.

If you have questions or need additional assistance, please contact us at marketing@capitalizeconsulting.com

Cognos Tutorial: How to Add a Second Line Per Record for Long Fields Using Grouping and Footers

We’re excited to add an educational component to our blog! Our consultants are great informational resources, and we want to share their knowledge with you, our subscribers. We’ll update our blog regularly with various educational posts written by our Business Intelligence team. We hope you find them to be informative and useful.

If you have suggestions for future blog topics, we want to hear them! Email us at marketing@capitalizeconsulting.com with your ideas!

 

IBM Cognos Tutorial

Cognos Tutorial:  How to Add a Second Line Per Record for Long Fields Using Grouping and Footers

For certain longer fields, such as addresses, notes, and comments, you want to format a second line per record.   This tutorial shows you how to create a second line using Grouping and Footers.

Create a List Report and include the Long Field in the List.  In this case, the Long Field will be the Retailer Site Address.

Here is the Current Report Output:

 

Select Retailer Site Address and click the Group/Ungroup button to Group the list by Retailer Site Address.

Select down arrow next to the Headers & Footers button and select “Create Footer”.

Here is the newly added Footer for Retail Site Address:

The Retailer Site Address Field is not needed anymore.  Select the Retailer Site Address Column and cut it from the list, leaving only the Retailer Site Address Footer.

Select the Retailer Site Address Footer and format it so it matches the rest of the list report.

Let’s add a Text Item before the Retailer Site Address so we know what the field is.  Unlock the Report.

Drag over a Text Item in front of the Retailer Site Address Field.

Type in “Site Address:” and click OK.

Bold the Text Item and Relock the Report.

Add a Border under the Footer to separate the records from each other.

Run the Report and see your new two lines per record List Report.

If you have questions or need additional assistance, please contact us at marketing@capitalizeconsulting.com