How can I make the following code work? It defines a function to calculate the cost over two periods into two variables and then substract the values of the two variables to produce a result that I want to show.
import "date"
import "timezone"
option location = timezone.location(name: "Africa/Johannesburg")
// Define start times
startTime = date.truncate(t: now(), unit: 1mo)
startToday = date.truncate(t: now(), unit: 1d)
// Function to calculate cost
calculateCost = (start, stop) => {
return from(bucket: "rpict3t1_1sData")
|> range(start: start, stop: stop)
|> filter(fn: (r) => r["_measurement"] == "rpict3t1_01")
|> filter(fn: (r) => r["_field"] == "P1" or r["_field"] == "P2" or r["_field"] == "P3")
|> integral(unit: 1s)
|> map(fn: (r) => ({ _value: (r._value / 1000.0 / 3600.0 * 220.0) }))
|> sum()
|> map(fn: (r) => ({ r with _value: ((float(v: r._value <= 600) * r._value * 1.8536) + (float(v: r._value > 600) * ((600.0 * 1.8536) + (r._value - 600.0) * 2.9516)) + float(v: date.monthDay(t: now())) * 4.80) * 1.15 }))
}
// Function Difference between two values
substract = (x, y) => x * y
// Calculate total cost up to the day before
totalCostBeforeToday = calculateCost(startTime, startToday)
// Calculate total cost up to the current time
totalCostCurrent = calculateCost(startTime, now())
// Calculate the additional cost for the current day
additionalCostToday = totalCostCurrent - totalCostBeforeToday
// Output the additional cost for the current day
additionalCostToday
@Mufika There were a few syntax errors in your original query. Flux doesn’t support positional parameters, only named parameters so you have to include the parameter names in your functions calls. For example:
As your query stands, your custom calculateCost() function returns a stream of tables. To do the operation you’re hoping to do, you need to update the function to return a scalar value. You can use findColumn to return an array of values in the _value column and then reference an element in that array.
With that in place, you can use the array package and array.from to build and return an ad hoc table with the computed value:
import "array"
import "date"
// Define start times
startTime = date.truncate(t: now(), unit: 1mo)
startToday = date.truncate(t: now(), unit: 1d)
// Function to calculate cost
calculateCost = (start, stop=now()) => {
valueArr = from(bucket: "rpict3t1_1sData")
|> range(start: start, stop: stop)
|> filter(fn: (r) => r["_measurement"] == "rpict3t1_01")
|> filter(fn: (r) => r["_field"] == "P1" or r["_field"] == "P2" or r["_field"] == "P3")
|> integral(unit: 1s)
|> map(fn: (r) => ({ _value: (r._value / 1000.0 / 3600.0 * 220.0) }))
|> sum()
|> map(fn: (r) => ({ r with _value: ((float(v: r._value <= 600) * r._value * 1.8536) + (float(v: r._value > 600) * ((600.0 * 1.8536) + (r._value - 600.0) * 2.9516)) + float(v: date.monthDay(t: now())) * 4.80) * 1.15 }))
|> findColumn(column: "_value", fn: (key) => true)
return valueArr[0]
}
// Calculate total cost up to the day before
totalCostBeforeToday = calculateCost(start: startTime, stop:startToday)
// Calculate total cost up to the current time
totalCostCurrent = calculateCost(start: startTime)
// Calculate the additional cost for the current day
additionalCostToday = totalCostCurrent - totalCostBeforeToday
// Output the additional cost for the current day in a table
array.from(rows: [{_time: today(), additionalCostToday: additionalCostToday}])
Note: I made a few other updates to your script:
Removed the timezone package. This only affects the behavior of window() and how it defines window boundaries across time changes. You don’t use window() in your query so there’s no need for it.
Updated the calculateCost() function to include a default value for the stop parameter.
Removed the subtract() custom function since you weren’t using it.
