Overview
Every temporal field has an associated native granularity. This defines the level of detail of the data in that field. For example, a field containing [2021‑01‑02, 2021‑01‑03, 2021‑01‑08...] has daily granularity, whereas a field containing [2021‑01‑01, 2021‑02‑01, 2021‑03‑01...] has monthly granularity. The granularity of a field can be set up using Automation Settings. This becomes what we call the “native granularity” of the field.
While plotting a chart, however, Yellowfin’s charting engine can aggregate data and plot it using a higher granularity. For example if we’re plotting Invoiced Amount by Start Date and the native granularity of a Start Date is “Day”, Yellowfin can aggregate the daily Invoiced Amount data for each month and plot Invoiced Amount per month. The data table for this chart will still have daily data.
The output produced by aggregating the data this way could be incorrect when advanced functions are used in the report. For example if we plot Invoiced Amount as a percentage of max by Start Date per month, the chart builder would aggregate percentages of max for each month which would yield an incorrect result. A similar situation arises on plotting a count by date.
Date granularity and Guided NLQ
Guided NLQ identifies such situations and prevents the user from asking a question with an invalid granularity selection. The interface prevents this by either not providing other granularity options, or by displaying an error message next to the run button. The situations where this does occur are:
- Compare metrics or segments by date, as running total.
- Variance by date.
- As percent of max/field by date.
- Count by date.
- Highlights when a date field is involved:
- Periodic average.
- Trends.
- Outliers.
- Step changes.
- Stepped average.
A way around this limitation is to set up a date hierarchy in the view. If the view has fields containing daily, monthly, quarterly and yearly temporal data, a drill down hierarchy may be set up from the field having the highest to the one having the lowest granularity. In Ski Team (for new installs), this has been set up as Year -> Quarter -> Month -> Invoiced Date. Each field has been set up with the corresponding granularity value. This can be set up for any date hierarchy. The conditions the hierarchy should meet are:
- The hierarchy must contain only date fields.
- All fields must use the Date Formatter or Timestamp Formatter.
- Granularities must be set up for each field in the hierarchy.
- The hierarchy should drill from the highest to the lowest granularity.
When these conditions are satisfied, only the field at the base of the hierarchy (in this case Invoiced Date) will be available for selection in NLQ. Every granularity in the date hierarchy (day, month, quarter, year) are considered as “native granularities” of Invoiced Date. The situations listed above, which restrict granularity, will provide these native granularity options as well.