Jump to content

Create a Scorecard with Last 7, 14 , & 365 Days

Recommended Posts

Hi George,

This is a common question.  The trick is to create a condition that includes capsules for the last 7, 14, and 365 days and then use the "Condition" process type in the Scorecard metric.  Note that these time periods could be whatever you like.

The first step is to create the condition.  You will do this in Seeq Formula with this code:

//Define a search window to look for now
$searchPeriod = capsule('2018-12-01T00:00-08:00', '2020-01-01T00:00-08:00')

//Identify now by finding the last available measured time stamp
$now = $a.validvalues().toGroup($searchPeriod).last().getKey()

//Create conditions representing the last 7, 14, and 365 days
$Last7DayCapsule = capsule($now-7d, $now).setProperty("Time","Last 7d")
$Last14DayCapsule = capsule($now-14d, $now).setProperty("Time","Last 14d")
$Last365DayCapsule = capsule($now-365d, $now).setProperty("Time","Last 365d")


The first section, in green, creates a period in which Seeq will search for "now."  The second section, in blue, defines "now" as the timestamp of the last valid value of variable "a" within the search window.  Seeq recommends using "Area A_Temperature" (included in your example data) as your variable "a."  The third section, in purple, creates the capsules representing the last 7, 14, and 365 days.  In this section, we are also creating a property on these capsules called "Time" and using this property to include text indicating the duration of the capsule.  Finally, in the section in red, we combine these three capsules into one condition.  

The next step is to create the scorecard metric.


To make your column header more concise, select "Capsule Property" as your column header and use the "Time" property created in the condition formula.  You can add additional metrics as desired.  The final product should look like this:


Edited by Julianne
  • Like 2
Link to comment
Share on other sites

  • 1 month later...

Quick follow-up question:

I want to do a scorecard with the following time periods:

  • So far today (midnight last night to now)
  • Yesterday (midnight 2 nights ago to 11:59pm last night)
  • So far this month (this month 1st at midnight up to now)
  • Last month (last month 1st at midnight up to last day of last month 11:59pm)

I’m guessing I’ll be using code similar to that shown above, but how do I write the purple part to do the different times? Is there a function like in excel that would for example tell me today’s month like this MONTH(NOW())?

Thanks in advance!

Link to comment
Share on other sites


Great question! The green and blue sections of the code will be the same, but we will need to add a few additional variables (in orange) before we define our capsules.  The new code will look like this:

//Define a search window to look for now
$searchPeriod = capsule('2018-12-01T00:00-08:00', '2020-01-01T00:00-08:00')

//Identify now by finding the last available measured time stamp
$now = $a.validvalues().toGroup($searchPeriod).last().getKey()

//Define additional variables with helpful timestamps
$midnight = $now.floorTime(1d)
$yesterday = $midnight - 1d
$month = $now.floorTime(1mo)
$month2 = $month - 1month
//Create capsules from the timestamp variables and combine them into one condition
   capsule($month2 , $month).setProperty("Time", "Last Month"),
   capsule($month, $now).setProperty("Time", "This Month") ,
   capsule($yesterday, $midnight).setProperty("Time", "Yesterday"),
   capsule($midnight, $now).setProperty("Time", "Today"))
Note that we are combining the red and purple steps above.  In the next release of Seeq, we will have a now() function, so you will be able to skip the green and blue steps and just set $now = now().  
Let me know if you have any additional questions!
Edited by Julianne
  • Like 1
Link to comment
Share on other sites

  • 3 months later...

Hi, regarding the question and your replies, i understand that we require a variable to create the condition. 

But is there a way to create the condition independent of any variables? something similar like Periodic Condition.

Link to comment
Share on other sites

  • 9 months later...
  • Seeq Team

This Use Case became simpler in R21.0.41 with the addition of the now() function in formula. You no longer need to do the first two steps of defining a search window for now and then using a signal to identify the last measured time stamp. Instead you just need to use the now() function to define the capsules and create your condition...

//Create conditions representing the last 7, 14, and 365 days
$Last7DayCapsule = capsule($now()-7d, $now()).setProperty("Time","Last 7d")
$Last14DayCapsule = capsule($now()-14d, $now()).setProperty("Time","Last 14d")
$Last365DayCapsule = capsule($now()-365d, $now()).setProperty("Time","Last 365d")



  • Like 3
Link to comment
Share on other sites

  • 9 months later...

Hi Stephanie,

yes, there a multiple solutions. I guess the easiest one is using the shifts() function:

//Create shift at 05:30 and combine with shift at 17:30
shifts(5.5, 12h).combineWith(shifts(17.5, 12h))

You could also use periods together with move() or a startdate of the initial shift

//Create a 12h capsule every 12hs and move to 05:30 / 17:30
periods(12h, 12h).move(5.5h)
//Create a 12h capsules every 12hs based on the specified date
periods(12h,12h, '2021-01-01T05:30:00+01:00')

In order to create just two capsules based on the current date you may use the following formula. As it uses now() the capsules have an uncertain state.

//Get timestamp for today and yesterday at midnight
$today = now().floorTime(1d)
$yesterday = $today - 1d

//create capsules
condition(12h, capsule($yesterday + 5.5h, $yesterday + 17.5h), capsule($yesterday + 17.5h, $today + 5.5h))

This is what it looks like in Workbench




  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...