Knowledge Drop

Why are my drills on running_total showing only that row?

  • 7 July 2021
  • 0 replies
  • 15 views

Userlevel 4

Last tested: Aug 6, 2020
 

running_totals are post-generated after the query is run, and so when we drill, we only see the results generated for that row in the backend SQL Query rather than what we're seeing on the front end.

There is a way to create a drill though through the html/link parameter with some URL hacking!

These are the steps I followed for this workaround:

 

  1. "Build out" the explore you want in the drill modal. i.e if the user clicks on the running_total and they want the drill to show ID and User Name, build out a new explore with just ID and User Name.
  2. Apply a Custom Filter to this new Explore; since running_totals capture the current month and all months before it, we'll want to translate that into the filters for our drill. I set matches advanced on the date field and set two place holder date values:

    Screen Shot 2020-08-06 at 12.03.39 PM.png
  3. Once you have the explore built out and have the generic filter, click the Gear by the Run button, select "Share" and grab the "Expanded URL".
  4. Take the expanded URL and decode that bad boy; we're gonna need to use this in the "link" or "html" parameter
  5. In the decoded URL, we should see the filter populate to something similar like: &f[order_items.returned_date]=before+2020/08/06,2020/08/06&limit...

    We'll want to take the 2020/08/06 and replace em with the Liquid value of the date field we're trying to drill into on the main explore. So something like:
    &f[order_items.returned_date]=before+{{ order_items.returned_date._value }},{{ order_items.returned_date._value }}&limit...
  6. Next, create a link parameter and use a relative link for our newly hacked URL
    link: {

label: "click me 3"

url: "/explore/andrew_ecomm/order_items?fields=order_items.id&f[order_items.returned_date]=before+{{ order_items.returned_date._value }},{{ order_items.returned_date._value }}"

}

 

This should allow the drill to carry over the dates as expected! Note, the only thing we may need to keep in mind is passing in existing filters into this new link!

 

This content is subject to limited support.                

 

 


0 replies

Be the first to reply!

Reply