Flux: Cumulative Product

InfluxDB 2 Dashboards
,
1

Hi Team,

How can I create column B such that column B shows the product of consecutive values of column A please?

cc: @scott , @Anaisdg , @grant1 tagging for attention please :pray:

2

Hey Scott, @scott , any idea on the above please?

3

Hello @ajetsharwin,
Thank you for your question and sorry for the delay. Sometimes we don’t get to questions over the weekend.
If your data is stored at regular intervals and you’re wanting to use Flux then you could:

  1. time shift your data
  2. join your data so you have a column with the original data and the timeshifted data in the same table
  3. map over the two columns and multiply the two values from each column and store the result in a new column

If your data isn’t at regular intervals or you fail to record a value there isn’t a way to achieve this with Flux.

I’d recommend using a client library. Maybe Python and using Pandas.

4

Hey Anais,

Given the roadmap of Influx 3 and Flux, I’m getting back to use InfluxQL for queries.

With respect to CUMULATIVE PRODUCT, we can calculate this by taking the exponential of the cumulative sum.

I’m trying to implement the below InfluxQL query which calculates the cumulative product but am facing the mixing aggregate and non-aggregate queries is not supported influxql error, could you please advise on how to best tackle this?

Query:

SELECT EXP(CUMULATIVE_SUM(qty_pct_change_ln)) as quantity_change, quantity
FROM “quantity_management”.“group-products”
WHERE time > now()-7h and product_id = ‘ABC’
GROUP BY product_id

cc: perhaps @Jay_Clifford or @scott can help too :pray:

5

@ajetsharwin It’s because you’re not applying any functions to the quantity field, so InfluxQL doesn’t know what to do with it. If you remove quantity from the SELECT statement, it should work.

SELECT EXP(CUMULATIVE_SUM(qty_pct_change_ln)) as quantity_change
FROM “quantity_management”.“group-products”
WHERE time > now()-7h and product_id = ‘ABC’
GROUP BY product_id

I know this probably isn’t ideal, but it’s one of the quirks of InfluxQL. Really, neither EXP or CUMULATIVE_SUM should be considered an aggregate function since they don’t reduce the number of rows returned by the query. This might be an InfluxQL bug.

6

Thanks @scott, is there anyway in which I can query both quantity and quantity change in the same query? or do they have to be queried using two queries?

Can you please advise where I can best raise this bug?

7

I think, currently, they’d have to be queried with two separate queries. Probably the best place to report the bug would be on the InfluxDB repo.