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:

image

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:

image

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.:

image

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:

image

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:

image

and correct application of the filter on the data set:

image

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:

image

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 
then INVOICE_NO 
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.

image

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.