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

[Curious George]

Hi Seeq,

I am trying to create a single scorecard in R21 with multiple metrics calculated over the last 7, 14, and 365 days, but I can't figure out how to do this.  Could you please provide some guidance? 

[Julianne]

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")

condition(370d,$Last7DayCapsule,$Last14DayCapsule,$Last365DayCapsule)

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 December 21, 2018 by Julianne

[Curious George]

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 1^st at midnight up to now)
• Last month (last month 1^st 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!

[Julianne]

George, 

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

condition(32d,

   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 February 1, 2019 by Julianne

[LRM]

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.

[Morgan Bowling]

Hi LRM 

I'm not sure I fully understand the question.  Can you elaborate a little bit? 

Morgan

[Krista Novstrup]

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")

condition(370d,$Last7DayCapsule,$Last14DayCapsule,$Last365DayCapsule)

 

[STEPHANIE.PLUT@LUBRIZOL.CO]

Is there a way to write a formula to create two different capsules. The first for 5:30AM-5:30PM of the previous day and the second for 5:30PM-5:30am of the current day?

[Thorsten Vogt]

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

Regards,

Thorsten