KPI Breakout in Specific Time Groupings

Is there any advice on formula or worksheet construction to build the data layout in the attached image? within SQL it's a series of UNION ALL statements, each filtered for the date range indicated (FYI...Last Week = Yesterday - 7 Days or same day last week). I'd love to avoid a ton of extemporaneous worksheets for this...any advice?

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hey Jason Westenskow - It might be challenging to achieve the exact format you've shared, however, you'll probably be able to create these numbers without multiple worksheets.  My initial thought is to create a separate search for each of these using the date keywords and pin the headline to the pinboard.  The first screenshot shows an example of headlines pinned to a pinboard.  Most of these KPI's can likely use standard date keywords.  For example, the search for the YTD KPI could be 'Calls RCV year to date'.  The Last Year YTD KPI will need a formula.  Here's an example of the syntax for the formula I used to create the KPI in the attached screenshot:

    sum(if (day_number_of_year ( add_days(date ,-365) ) <= day_number_of_year ( add_days(now (),-365 ) ) and year(date ) = year (now())- 1 ) then sales else 0 ) 

    I'd double check that this formula is doing what you want it to by bringing daily into the search.  You should only see numbers next to the last year ytd days.

  • @ Micah Terry. Thanks for the quick response! Any way to get more of these type of features added to the product? Search is great, but snapshots and executive dashboards as well as dashboards mounted on Wallboards/TVs in call centers or at corporate are better with these type of Big Number data points. Additionally, we've found that the ability to base conditional formatting on a calculation and not a hard-coded value are very useful.

    Like 1
  • Jason Westenskow - I've requested KPI callouts/Boxes under feedback. I believe it's on the roadmap. This type of visualization is available across most BI tools. For TS - pinning headliners work when you don't have a chasm trap. 

    Like 1
Like Follow
  • Status Answered
  • 2 yrs agoLast active
  • 3Replies
  • 752Views
  • 4 Following