DVD: Filtering Measures based on conditions

Within Analytics we can define different types of Measures like discrete (raw) Measures or percentage or ratio Measures. For discrete measures we can also define a base measure and derived measures. E.g. within the HR domain we can define a Headcount Metric as the base metrics and a male, female and diverse Headcount as the derived measure. When adding up all male, female and diverse it should be identical with the (overall ) Headcount. Another example is within the Procurement domain where we can define an invoice amount and a invoice paid and invoice unpaid derived measure:


This break-down allows to further understand how a certain metrics is comprised.

Within Oracle Data Visualization there are two possible options to achieve this filtering.

  1. Using the Filter operator
  2. creating a case when statement

Using the Filter operator

The first option is to use the Filter operator within the formula editor:


Within the formula editor, a measure can be defined from a list of available measures, but it’s also possible to define new measure using a count aggregation on a attribute column like Invoice Number and add a filter on the dimension e.g. Paid Yes or No.:


However, when using this Filter function with a newly defined measure (like above using a count aggregate on Invoice Number) the filter is not applied in DVD, as can be seen in below screenshot were the count for # of invoices is identical to the count of # of invoices paid:


This might need to be considered a Bug, since this should apply the filter to the defined measure.

When using an available measure from the drop down list, the filter is correctly applied. E.g. when using the Value measure (representing the amount of the invoice) the filter can be applied for paid/unpaid using the Payment date attribute:


and correct application of the filter on the data set:


Thus, the Filter operator may only correclty work when using a actual measure available from the drop-down list and not with newly defined measures “on-the-fly” in the formula editor.

The alternative is to use a case when statement:

Case when statement

Since, the Filter operator doesn’t evaluate the filter correctly, the other option is to use a case when statement that counts the number of invoices if an invoice has been paid like below:


This formula checks if a Payment date is set and if a payment date is set then it uses the invoice Number for counting:

COUNT(DISTINCT case when PAYMENT_DATE is not null 
else NULL end)

This formula (based on the case when statement) applies the intended filter on Payment date correctly and allows to calculate the derived measure.


To conclude

To apply filter for a measure that is not available from the data-set (defined in the Prepare Tab as a measure) use the case when logic to calculate based on a condition.

If a measure is available you may also use the predefined Filter operator from the formula editor.

complexity and benefits with the climate action tracker

The climate action tracker is a simple thermometer indicating the effect from C02 emission on the expected increase in global avg. temperature. The increase of avg. temperature is shown for two models:
1) current policies
2) pledges (considering intentions)







(source: new climate institute) 

The new climate institute responsible for the action tracker looks closely at the current policies and proposals of each country as part of international climate negotiations. The institute calculates the effect on emissions per country and then use a simplified climate model to aggregates the emissions on a global level to predict the temperature: CAT_Graph_global_150712_for_website-01
(source: new climate institute)   

The problem is that the proposals of each country are not easy to understand, sometimes because it is naturally technically complex, but sometimes also because it is intentionally vaguely formulated to avoid a exact definition.

This complexity is why it needs an effort like the climate action tracker in order to interpret the proposals and to make it easy to understand what they mean.

This example can easily be transferred for business applications where the data is often complex to understand requiring an instance to interpret the data using KPIs making it easier to understand the business and the impact each KPI has on the success of the company.

further information: