(This posting follows an earlier posting on working with EQL to use a join instead of a where clause)
The last element to working with metrics I want to cover relates to a side effect of the activity of Data Exploration. As you browse your data and navigate through different dimensions you can arrive at a point where the records needed to define the metrics are no longer returned.
This is the same behavior we see with the Guided Nav component. It’s very effective as you explore the various dimensions that records start to be filtered out. Dimensions aren’t displayed if you can’t explore the records to any further levels. Allows you to nicely focus in on records of interest.
Using the GettingStarted data domain, browse to “Fiscal Year” and select 2011 & 2010.
The metrics bar provided with this sample will display values, however we know there are some issues. Firstly the sales growth isn’t reflective of 2010. Secondly though the number of orders is, there’s no alignment between those order numbers and the calculated sales growth.
The metrics bar we created earlier shows us data more contextually relevant to our current nav state.
If we remove 2011 from our breadcrumbs pane our updated metrics bar unfortunately fails us. They both do actually, though ours arguably worse than the original version.
So why does this happen? We have Coalesce calls around the attributes to handle nulls, so shouldn’t we simply see 0 values? It seems to work for the default metrics bar after all.
The issue for our eql is we’re defining record sets for our source and we’re not getting any records. Coalesce operates on an attribute value. It handles a value missing from a record, not a missing record. If you don’t even have a record in your record set then Coalesce has nothing to execute against. This doesn’t seem to impact the eql that operates by default against the Base view.
Currently our eql looks as follows, leveraging both the dynamic identification of fiscal years and the performance issue around using a WHERE clause. The latter change was less important for GettingStarted, but I included it for anyone wanting the reference.
There may be other solutions, however the approach I took to resolving this applies the same approach for resolving the performance issues around filtering the fiscal year. Taking a page from the world of traditional database development I just made sure I always had a record set of 1 that I could join to. This way my Coalesce functions will always execute.
You can just use a local DEFINE statement. In order to reuse the logic multiple times I created a view as follows:
This view will always and only ever provide me with a “RecordCount” of 0.
Applying this to my eql I am creating a Cartesian product by doing a full outer join to the ReturnZeroCount recordset. This however has minimal impact since its only a single record with a single value. And where its added its only adding a value of 0 so won’t change an actual count. Really makes you wonder how ancient civilizations managed without the concept of 0!
Since I’m always guaranteed to have at least 1 record in my record set I also know my metrics will always be calculated. They may only return a value of 0, but that’s a better message than “No results available”.
The result to displaying my metrics bar is as follows:
There are two final refinements to make. Because I no longer have a previous year my join from rs1 to rs2 fails. By adjusting it to a FULL JOIN as well I can make sure I’m not excluding values for the current fiscal year.
I’ll also update the calculation of Sales Growth, since SalesPrevious may be null then I’ll default the value to 1. This is a bit pointless but you could argue it’s appropriate to identify 100% sales growth when starting from zero sales.
Now my metrics bar displays my total sales for the current year, my total number of orders for the current year, and my sales growth from the previous year. Values that will correctly reflect your changing navigation state.
Hopefully these techniques will prove of use to you in your business. Cheers!