Ubidots Community

Cumulative Sum of Variable by Day of the Week

Hi All,

I was wondering if there is a way to set up a synthetic variable by day of the week. I am looking to then make a bar graph that shows the cumulative total of food digested by our machine per day, so we can begin programming our machine to have different techniques per day of the week (and amount of food that it’ll likely be seeing) I created an excel spreadsheet as an example of what we’d like to do. It is pretty simple I just can’t figure out if Ubidots supports a day of the week aspect of synthetic variables…
Thanks!

@psalibe

Yes, you can create a synthetic variable that creates a sum for each day. sum(x, data_range) : Returns the summation of the dots in the specified data_range

More on this page: Analytics: Synthetic Variables Basics | Ubidots Help Center

Hope that helps,

Chip

I’d like to have it automatically update the summation of each day of the week, as opposed to specifying a date range. So for example next Monday say they load another 50 lbs, I’d like it to add to the all time Monday total. Using the sum(x, data_range) wouldn’t I have to manually add one each day?

Would I do something along the lines of…

sum(x, "1D", offset=0)

and then

sum(x, "1D", offset=24)

and so on, adding 24 hours for every day of the week?

I may have misunderstood your requirement as I am not sure why you need the offset. Perhaps we could see if someone else might weigh in. Sorry, Chip

@psalibe ,

I think I now understand your question (perhaps?) because I cam across a similar need.

Is this what you need : A way to build a synthetic variable that is aware of the day of the week.

After looking here, I don’t see a “day of week” date range or variable. Perhaps I am missing something.

Thanks,

Chip

Good day @psalibe

About the question you made in the past months, if you want to do this through synthetic variables, it could be a bit more complicated, since you would probably need to send each dot with a key context that indicates the day of the week and this would also imply using a lot of synthetic variables to be able to compute the accumulated of each day. For this kind of problem, I would recommend using Ubifunctions. Below I share the code of a function to compute an accumulated summation for each day of the week as you desire:

//CONST DEFINITION
const axios = require('axios');
const BASE_URL = 'https://industrial.api.ubidots.com'
const TOKEN = 'YOUR-UBIDOTS-TOKEN-HERE';
const deviceLabel = 'YOUR-DEVICE-LABEL-HERE';
const variableLabel = 'YOUR-VARIABLE-LABEL-HERE';

//VARIABLE LABELS FOR EACH WEEKDAY
const ACCUMULATED_VARIABLES = {
  0: {label: 'acc_sundays', id: 'ID_sundays'},
  1: {label: 'acc_mondays', id: 'ID_mondays'},
  2: {label: 'acc_tuesdays', id: 'ID_tuesdays'},
  3: {label: 'acc_wednesdays', id: 'ID_wednesdays'},
  4: {label: 'acc_thursdays', id: 'ID_thursdays'},
  5: {label: 'acc_fridays', id: 'ID_fridays'},
  6: {label: 'acc_saturdays', id: 'ID_saturdays'}
}

async function main(args) {
  
  //INITIALIZE CUMULATIVES
  var CUMULATIVES = {
    'monday': 0,
    'tuesday': 0,
    'wednesday': 0, 
    'thursday': 0,
    'friday': 0,
    'saturday': 0,
    'sunday': 0
  }

  //GET ACTUAL TIME
  let [initTimestamp, finalTimestamp] = getTimestampRange();

  //QUERY PARAMS TO GET ALL DATA
  var queryParams = {
    start: initTimestamp,
    end: finalTimestamp
  }
  
  //GET ALL DATA
  var responseAll = await getData(deviceLabel, variableLabel , queryParams, TOKEN);
  
  //DAYS WITH CHANGES
  var weekdays = [];
  responseAll["results"].forEach((value) => {
    let actualTimestamp = new Date(value['timestamp']);
    let day = actualTimestamp.getUTCDay();
    if (!weekdays.includes(ACCUMULATED_VARIABLES[day]['id'])) {
      weekdays.push(ACCUMULATED_VARIABLES[day]['id']);
    }
    let actualVariableLabel = ACCUMULATED_VARIABLES[day]['wday'];
    CUMULATIVES[actualVariableLabel] += value['value'];
  });

  //QUERY PARAMS TO GET CUMULATIVES LAST VALUES
  var queryParams = {
    fields: 'lastValue,label',
    id__in: weekdays.toString()
  }

  //GET CUMULATIVES 
  var responseCumulatives = await getCumulatives(queryParams, TOKEN);
  
  //CREATE THE PAYLOAD TO SEND TO UBIDOTS
  var payload = {};
  responseCumulatives["results"].forEach((element) => {
    var variableLabel = element['label'];
    var lastValue = element['lastValue']['value'] + CUMULATIVES[variableLabel];
    payload[variableLabel] = lastValue;
  });
  
  //POST A UBIDOTS
  var postResponse = await ubidotsUpdateDevice(deviceLabel, payload, TOKEN);
   
  return postResponse;
}

//GET TIME
function getTimestampRange() {
  let now = new Date();
  let hour = now.getHours();
  now.setMinutes(0);
  now.setSeconds(0);
  let finalTimestamp = now.setMilliseconds(0);
  let initTimestamp = now - 3600000;
  return [initTimestamp, finalTimestamp];
}

//GET FROM UBIDOTS (RAW VARIABLE)
async function getData(deviceLabel, variableLabel, queryParams, token) {
  var url = `${BASE_URL}/api/v1.6/devices/${deviceLabel}/${variableLabel}/values`;
  var config = {
    headers: {'X-Auth-Token': token },
    params: queryParams
  };
  var response = await axios.get(url, config);
  return response.data;
}

//GET FROM UBIDOTS (CUMULATIVES)
async function getCumulatives(queryParams, token) {
  var url = `${BASE_URL}/api/v2.0/variables/`;
  var config = {
    headers: {'X-Auth-Token': token },
    params: queryParams
  };
  var response = await axios.get(url, config);
  return response.data;
}

//POST TO UBIDOTS
async function ubidotsUpdateDevice(deviceLabel, payloa, token) {
  let config = {
    method: 'POST',
    url: 'https://industrial.api.ubidots.com/api/v1.6/devices/' + deviceLabel,
    data: payload,
    headers: {
      'Content-Type': 'application/json',
      'X-Auth-Token': token
    }
  }
  const response = await axios.request(config);
  return response.data;
}

Firstly, you need to define the constants of your UbiFunction:

  • Axios library and the BASE_URL are necessary to perform the requests.
  • The TOKEN, deviceLabel and variableLabel must be replaced with the specific data of your account (the latters are the labels of the device and the variable from which the raw data will be retrieved).
//CONST DEFINITION
const axios = require('axios');
const BASE_URL = 'https://industrial.api.ubidots.com'
const TOKEN = 'YOUR-UBIDOTS-TOKEN-HERE';
const deviceLabel = 'YOUR-DEVICE-LABEL-HERE';

ACCUMULATED_VARIABLES is an object that specifies every weekday accumulated variable label and each variable ID for every accumulated variable. You need to replace the id portion with what will be the accumulated variables in your account.
Make sure to create the accumulated variables with the labels herein:

//VARIABLE LABELS FOR EACH WEEKDAY
const ACCUMULATED_VARIABLES = {
  0: {label: 'acc_sundays', id: 'ID_sundays'},
  1: {label: 'acc_mondays', id: 'ID_mondays'},
  2: {label: 'acc_tuesdays', id: 'ID_tuesdays'},
  3: {label: 'acc_wednesdays', id: 'ID_wednesdays'},
  4: {label: 'acc_thursdays', id: 'ID_thursdays'},
  5: {label: 'acc_fridays', id: 'ID_fridays'},
  6: {label: 'acc_saturdays', id: 'ID_saturdays'}
}

At the end, you need only to configure the UbiFunction to be triggered every 60 minutes:

image

NOTE: bear in mind that this UbiFunction will only start accumulating from the moment you deploy it. If you raw variable contains historical data, this won’t be retroactively considered.

All the best,
Ángela