Building a filter-selective calculation with Oracle Analytics - Part II

In our previous post here, we discussed a technique to build custom metrics in Oracle Analytics Self Service (DV, no RPD modeling) that would not obey to filter selections. Our use case was a a global revenue canvas where, if a user selects a specific country from a canvas filter, the metric % of Total Global Revenue would remain showing ratio of this country's revenue vs the whole global, unfiltered revenue.

We achieved this by duplicating the dataset in DV, defining a 'full-outer' join between our two datasets, and simply building a calculation with numerator and denominator each coming from respective datasets. That way the denominator in the ratio (% of Total Sales) could remain constant irrespective of filters on the numerator. So, functionally, what we delivered was : if user filter for France in the global revenue dashboard, Oracle Analytics will compute % of total Revenue as the division of France Revenue by total unfiltered Global Revenue. 

But what if our need had been to show revenue for France in 2019 as a ratio of Global revenue... in 2019 ? In this case, both numerator and denominator should still be filtered for a year in the time dimension, but both should not behave identically on geographies filter... The denominator should not be applied the filter for France in this case. 

With blog 'Part I' implemented, tweaking the model to achieve this new need is actually very simple. This short video walks you through the steps and the blog below describes them :

There is only one step to complete to achieve this, and is pretty simple : add an additional explicit match (join column) between the two datasets A and B. At the end of our previous blog, we had two identical datasets joined by a dummy column (1=1) :

All we need to do now is to add an explicit match on time dimension. That way the year filter will be applied to both datasets and our functional need will be resolved :

Once this is done, the canvas will now show Total Sales B (denominator) filtered by year -see $2.0M below- but not by Product Category. You can see below the amount of Sales for 2015 for just 'Technology' Product Category is only $764.3K :

So this canvas is really showing us the Percent of Sales that 'Technology' product line represented vs the total of Sales, but computing this ratio only for 2015. This is exactly the same use case as ratio of Sales made in France in 2019 as % of total Global Revenue in 2019.

Note that if we add more Matches (join columns) between our two datasets, we can easily extend the scope of what columns will the denominator be filtered on. For example, in the picture below, we are adding Order Priority column :

As a result, the whole canvas will filter by Order Priority as well (including denominator coming from datasource B). In the picture below, both datasets A and B are filtered on Time and Order Priority (see value of Total Sales B = $521.5K), but only dataset A also filters on Product Category ($152.5K). The numerator and denominator are behaving accordingly for the ratio calculation at the bottom.

One important design remark

One important aspect to keep in mind for this design to operate correctly : the definition of joins between mashed-up datasets (A and B here) requires designers to specify the type of the join in each viz on the canvas. So, for each of the elements on the canvas, in the properties tab, you need to make sure that the 'blending' tab is configured with 'All Rows' for both datasets. See the picture below. This detail is important and will prevent the correct behavior to happen if it's not setup.

Thanks for your time reading this series of blog entries !