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.
- Using the Filter operator
- 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 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.
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.
