I’m trying to display a graph with dollar values on the Y axis and dates on the X axis. I need to show the most recent 13 records. Not the most recent 13 months from today, but the most recent 13 existing records. I can’t seem to find a way to do this.
The closest I’ve come is a graph grouping chronologically from oldest to newest, grouped by the Bill Date in the record, grouped by Month, using Show Latest: 13. But if they’re six months behind in getting bills… the graph shows only seven entries instead of starting with the most recent and counting back 13 records.
I solved this by adding 7 fields between the parent and child object. It’s possible this could be done in fewer steps, but I got it working this way and I didn’t see a real reason to consolidate it down.
My parent object lists “Tenants.” My child object lists multiple “Readings” for each Tenant. Each Reading has fields for a Reading Date and a Value. I wanted to graph the most recent 13 Readings for each Tenant. Not the last 13 calendar months, but the most recent 13 readings.
To do this we make Knack determine the Max reading date, then flag the Max reading date and the 12 reading dates before it.
Here are the 7 fields. I made my field names very informative to make it easier to follow and for me to understand in the future.
Readings Object (Child): Equation field, called “Graph Step 1: Reading Date for Max”. Equation Type: Date. Date Type: Hours. Result Type: Date. The equation itself is only the {Reading Date}.
Tenants Object (Parent): Max field, called “Graph Step 2: Max Reading Date (returns a Number)”. In “Field to Max” choose the {Graph Step 1} field.
Tenants Object (Parent): Equation field, called “Graph Step 3: Max Reading Date (converted to date)”. Equation Type: Date. Date Type: Hours. Result Type: Date. The equation itself is only the {Graph Step 2} field.
Readings Object (Child): Equation field, called “Graph Step 4: Max Reading Date in Epochs Unix.” Equation Type: Numeric. The equation itself is only the {Graph Step 3} field. This takes the Max reading date and converts it to an Epochs Unix value, which is (perhaps bizarrely) the number of milliseconds that have passed since 1/1/1970.
Readings Object (Child): Equation field, called “Graph Step 5: Reading Date in Epochs Unix.” Equation Type: Numeric. The equation itself is only the {Reading Date} field (not the max).
Readings Object (Child): (Note in advance: You don’t actually need this field, but it helped me in debugging my issues) Equation field, called “Graph Step 6: Reading Date + 13 Months.” Equation Type: Numeric. The equation for my use case is {Graph Step 5} + [34000000000]. That number is roughly 13 months in milliseconds, with a little shaved off because it was slipping a 14th month in sometimes due to my shifting dates. You’ll have to adapt this # of milliseconds to your own uses, but essentially this field says “If I add 13 months to my Reading Date, what would it be?”
Readings Object (Child): Equation field, called “Graph Step 7: Most Recent 13 Reading Dates Marked as 1”. Equation Type: Numeric. This is a simple If/Then field that says: IF a given Reading Date is within 13 months of the Max Reading Date, then mark it as “1”, otherwise mark it as 0. The equation is:
{Graph Step 5: Reading Date in Epochs Unix} > ({Graph Step 4: Max Reading Date in Epochs Unix} - 34000000000) ? 1 : 0
Now all you have to do is add your graph. Go to Pages, add a View with a chart, then in the Source section tell it to Show records that match:
{Graph Step 7} | is | 1
Then when you group your records in the Data section, group it by the Reading Date, chronologically in whatever order you prefer, and tell it to show All Dates. It’ll be limited by the Source section, so no need to limit it here.
As you add data, the Max moves, as does the 13 month window before the Max, so your graph will always include 13 months.
Hope that helps someone because it took me quite a while to sort out!