Aggregated running product or product of list measure

  • 16 November 2020
  • 2 replies

What I'm facing is: Every hours we take a value from our systems and store it on a dimension called results. I need to calculate a running_product of this values aggregated on a timeframe defined by the user. (value_1 * value_2 * value_3.....value_n)
I thought about creating a dynamic dimension and a parameter where user can select what timeframe he wants and than create a list measure using the results dimension so it would aggregate every single result on for a week (month, year or w/e was selected) and than create a table calculation with the product() function but the product() won't work since the list measure store values as strings.
Anybody ever had to deal with something similar?

This is how my data looks like
This is how the list measure looks like. I would like to apply a product() on it


2 replies

Userlevel 1

Are you saying the Concat Results dimension is a String? Or is it a Number?

Seems you could create the dynamic time dimension as you planned. Here’s a couple examples how:

Then you could create a measure on the Concat Results dimension like:

 measure: total_concat_results {
    type: sum
    sql: ${concat_results} ;;

Or if you need to convert to a number first:

  measure: total_concat_results {
    type: number
    sql: sum(cast(${concat_results} as float)) ;;

Then you’d add your dynamic time dimension and concat results measure to query. 

Does this make sense? Or am I misunderstanding what you ultimately need to see in the result?

The concat_result is a number dimension but the uptime_list is a string list and I can’t apply the product() function on it

 dimension: concat_results {
type: number
sql: ${results} + ${uuid} ;; #uuid is a random number
value_format: "0.00%"

measure: uptime_list {
type: list
list_field: concat_results


What I want is to group every concat_result value in user timeframe selection (week, month or w/e) and multiply them by each other (ie: value_1 * value_2 * value_3 * value_4).

Since the product() function can be applied to a number or a number list I tried making a list measure of the concat_results and than apply it but it actually convert it to a string list and not a number list