Volatile formula in Excel Xlcubed blog header

Warning: Excel Can Get Volatile (revised)

This is a revised version of this post: Warning: Excel can get Volatile

Excel is a great tool for dashboard/report delivery and design (it’s why we created our addin in the first place!), but there is a hidden performance trap:

Offset, Now, Today, Cell, Indirect, Info and Rand

If you’ve ever used any of these formulae, you may have noticed that whenever you change a cell, or collapse/expand a data grouping, Excel recalculates. That is because these are VOLATILE formulae, as soon as you use one of these, Excel will enter a mode where everything is always recalculating, and for good reason.

Continue reading “Warning: Excel Can Get Volatile (revised)”
Customer Segmentation XLCubed blog header

Customer Segmentation By Revenue Contribution

We recently had a customer asking us for some assistance with a business challenge.

He had been asked to provide a report analysing their customer base by % contribution to company revenue. But that wasn’t all! He also had to produce an additional report that tracked the customers ‘lost’ by the company Year on Year by looking at sales activity.

This approach is also often used when looking at profitability, and many businesses like to classify their customers in this way:

  • group ‘a’ of key customers make up 40% of our company revenue and group ‘b’ make up the next 30%
  • which customers have we ‘lost’ or have become inactive over a period

This is where XLCubed’s capability and flexibility comes into play – we have an advanced selection mode for handling exactly this type of scenario.

Continue reading “Customer Segmentation By Revenue Contribution”

Rolling Forecast Reporting In Excel XLCubed Blog Header

Rolling Actual and Forecast Reporting

It’s a very common requirement in financial reporting to show actual and forecast data by month across the entire year. Often, we are focusing on the expected year end position so we will want to use the actual data where it exists, and the forecast data where it doesn’t so that the year-end total becomes increasingly accurate through the year.

Excel is a flexible environment and people often build that type of report using a mixture of copy-paste and formulas. It achieves the goal but can be a cumbersome process needing quite a bit of manual amendment each month to update it.

The Challenge

When building this type of report the main challenge is normally that Actual and Forecast are two different measures, or members of a ‘scenario’ hierarchy. In most client reporting tools, reporting across multiple months will mean both actuals and forecast showing for every month where both exist, as shown below. In our case if completed actuals exist, that’s all we want to see, and then forecast for the remaining months.

XLCubed Grid in Excel showing Actual and Forecast Data for all months

We want our report to be dynamic so that we are not having to redesign it every time. In a previous blog from some years back, we showed you how to create the report using the “Keep – exclude from display” feature. While that approach still works, there is now a more intuitive way to achieve the same result using “Keep – Keep Members and Create Dynamic Set”

Continue reading “Rolling Actual and Forecast Reporting”
A View of Usage Metrics Report

Usage Metrics in XLCubed

With a wide range of reports being accessed and published daily, it is important for administrators and management to be able to keep abreast of their usage. Common questions are:

  • What reports are being published?
  • How many reports have been updated?
  • How long are they taking to open?
  • Who is publishing the reports?
  • Which reports are not being used?

Our latest 9.2.47 release provides an updated version of the Usage Metrics Dashboard for XLCubed Web which gives visibility on the questions above and more.

Continue reading “Usage Metrics in XLCubed”
Monitoring Data Connected Comments In Excel Blog Header

Monitoring Data Comments

Many BI and reporting tools now allow users to collaborate directly with a report. This avoids the cumbersome backwards and forwards of email chains or the need to meet in person.

As useful as this is, some organisations have concerns about the ability to monitor comments made. We will discuss this aspect in more depth after a brief introduction to comments and collaboration.

Continue reading “Monitoring Data Comments”
XLCubed showing an element we wish to remove an update the totals

Advanced Grid Reporting – MDX:

This weeks blog is for the more technically minded among our user base and our partner network – not for everyone, but highlights some potentially very powerful capabilities!

We will be looking at using MDX: and MDXSet: along with Query Generators to create sub-cubes, meaning we can develop advanced reports that would otherwise be difficult to achieve.

Continue reading “Advanced Grid Reporting – MDX:”

Linking it all together

As we’ve seen in the past, XL3Link is a flexible formula that is really worth knowing about to help easily build interlinked reports. To some extent it’s like a Swiss army knife – multi-purpose!  From highlighting a series on a chart to hosting a tooltip,  XL3Link is a flexible formula that can help make your reporting more interactive at multiple levels.

In this post we’ll be discussing a few more novel ways to use XL3Link.

Continue reading “Linking it all together”

Excel page with dynamic search

Member Searching To Create A Product Details Page

XLCubed is a great tool when it comes to member searching: it allows you to quickly and easily find members as well as dynamically update a report based on search parameters. Take a read of our last post to get started with this functionality.

The example in this post uses these searching methods to create a product search page. The left side provides search functionality for all the products contained in the cube, with a link to select the product which displays the full details on the right side.

Continue reading “Member Searching To Create A Product Details Page”

Combining Sets

XLCubed has lots of options for Ranking, Sorting and Filtering (And Searching. And Grouping. And, and and … 😉)

As well as all of these, one often overlooked aspect of the Advanced member selector is the member combination mode.

This enables lots of interesting scenarios, especially as you can run more advanced selections on the results of these.

Continue reading “Combining Sets”