Applied JAQL formula on top of another JAQL formula result

[enmiwong]

Hi,

I have applied a JAQL formula to get new values (the values * 10), after that, I want to get the MAX from the result of the first query. how to achieve that?

this is the result get from JAQL :

 [{"data": 4.3,"text": "4.3"}, {"data": 1,"text": "1"}, {"data": 43,"text": "43"}]

the third column is get by applied this JAQL formula: 

{
"jaql": {
"context": {
"[sepal_length]": {"dim": "[irisdataset6.sepal_length]"}
},
"formula": "10 * MAX([sepal_length]) + 0",
"title": "transform_x"
}
}

now I want to get the max from the third column, how to achieve that?

[Helena_qbeeq]

Hi @enmiwong ,

If I understand your question correctly, in the third column you would like to get the maximum value previously obtained on the rows.

Have you tried using multi-pass aggregation?

In your example, it would look like this:

MAX(DIMENSION_IN_FIRST_COLUMN, (10 * MAX([sepal_length]) + 0))

I am attaching an example based on the eCommerce sample data.

Please let me know if this works for you.

Always here to help,
Helena from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner

[DRay]

Hello @enmiwong,

I’m following up to see if the solution offered by @Helena_qbeeq worked for you.

If so, please click the 'Accept as Solution' button on the appropriate post, that way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

[enmiwong]

Hi, what is the DIMENSION_IN_FIRST_COLUMN ? is that a context key for the formula?

one more question, how to do filter based on the transformed values?

[Helena_qbeeq]

Hi @enmiwong ,

That would be the dimension that differentiates each row. Similar to a group by.

What kind of filters would you like to implement? On the formula itself? on the widget? on the dashboard?

Always here to help,
Helena from QBeeQ
[email protected]
QBeeQ - Gold Implementation and Development Partner

[enmiwong]

{
  "headers": [
    "histogram-default___x--sepal_length",
    "histogram-default___y--sepal_length",
    "transform_x"
  ],
  "metadata": [
    {
      "jaql": {
        "dim": "[irisdataset6.sepal_length]",
        "title": "histogram-default___x--sepal_length"
      }
    },
    {
      "jaql": {
        "agg": "countduplicates",
        "dim": "[irisdataset6.sepal_length]",
        "title": "histogram-default___y--sepal_length"
      }
    },
    {
      "jaql": {
        "context": {
          "[sepal_length]": {
            "dim": "[irisdataset6.sepal_length]"
          },
          "[sepal_width]": {
            "dim": "[irisdataset6.sepal_width]"
          }
        },
        "formula": "(1* MAX([sepal_length])' )+ (-1* MAX([sepal_width])) + 0",
        "title": "transform_x"
      }
    }
  ],
  "datasource": {
    "revisionId": "TBD",
    "fullname": "LocalHost/irisdataset6_PathWave_fa19bddef76d43688f7ae03eb5ef194f"
  },
  "processingInfo": {
    "cacheType": "result",
    "cacheTime": "2024-08-31T04:37:34.891Z"
  },
  "translationInfo": {
    "translationServiceProvider": "NewTranslationService",
    "isTranslationFallback": false,
    "isQueryFallback": false,
    "translationDuration": 0.008,
    "sqlFromCache": false
  },
  "values": [
    [
      {
        "data": 4.3,
        "text": "4.3"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 1.2999999999999998,
        "text": "1.3"
      }
    ],
    [
      {
        "data": 4.4,
        "text": "4.4"
      },
      {
        "data": 3,
        "text": "3"
      },
      {
        "data": 1.2000000000000002,
        "text": "1.2"
      }
    ],
    [
      {
        "data": 4.5,
        "text": "4.5"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 2.2,
        "text": "2.2"
      }
    ],
    [
      {
        "data": 4.6,
        "text": "4.6"
      },
      {
        "data": 4,
        "text": "4"
      },
      {
        "data": 0.9999999999999996,
        "text": "1"
      }
    ],
    [
      {
        "data": 4.7,
        "text": "4.7"
      },
      {
        "data": 2,
        "text": "2"
      },
      {
        "data": 1.5,
        "text": "1.5"
      }
    ],
    [
      {
        "data": 4.8,
        "text": "4.8"
      },
      {
        "data": 5,
        "text": "5"
      },
      {
        "data": 1.4,
        "text": "1.4"
      }
    ],
    [
      {
        "data": 4.9,
        "text": "4.9"
      },
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 1.3000000000000003,
        "text": "1.3"
      }
    ],
    [
      {
        "data": 5,
        "text": "5"
      },
      {
        "data": 10,
        "text": "10"
      },
      {
        "data": 1.4,
        "text": "1.4"
      }
    ],
    [
      {
        "data": 5.1,
        "text": "5.1"
      },
      {
        "data": 9,
        "text": "9"
      },
      {
        "data": 1.2999999999999998,
        "text": "1.3"
      }
    ],
    [
      {
        "data": 5.2,
        "text": "5.2"
      },
      {
        "data": 4,
        "text": "4"
      },
      {
        "data": 1.1000000000000005,
        "text": "1.1"
      }
    ],
    [
      {
        "data": 5.3,
        "text": "5.3"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 1.5999999999999996,
        "text": "1.6"
      }
    ],
    [
      {
        "data": 5.4,
        "text": "5.4"
      },
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 1.5000000000000004,
        "text": "1.5"
      }
    ],
    [
      {
        "data": 5.5,
        "text": "5.5"
      },
      {
        "data": 7,
        "text": "7"
      },
      {
        "data": 1.2999999999999998,
        "text": "1.3"
      }
    ],
    [
      {
        "data": 5.6,
        "text": "5.6"
      },
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 2.5999999999999996,
        "text": "2.6"
      }
    ],
    [
      {
        "data": 5.7,
        "text": "5.7"
      },
      {
        "data": 8,
        "text": "8"
      },
      {
        "data": 1.2999999999999998,
        "text": "1.3"
      }
    ],
    [
      {
        "data": 5.8,
        "text": "5.8"
      },
      {
        "data": 7,
        "text": "7"
      },
      {
        "data": 1.7999999999999998,
        "text": "1.8"
      }
    ],
    [
      {
        "data": 5.9,
        "text": "5.9"
      },
      {
        "data": 3,
        "text": "3"
      },
      {
        "data": 2.7,
        "text": "2.7"
      }
    ],
    [
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 2.6,
        "text": "2.6"
      }
    ],
    [
      {
        "data": 6.1,
        "text": "6.1"
      },
      {
        "data": 6,
        "text": "6"
      },
      {
        "data": 3.0999999999999996,
        "text": "3.1"
      }
    ],
    [
      {
        "data": 6.2,
        "text": "6.2"
      },
      {
        "data": 4,
        "text": "4"
      },
      {
        "data": 2.8000000000000003,
        "text": "2.8"
      }
    ],
    [
      {
        "data": 6.3,
        "text": "6.3"
      },
      {
        "data": 9,
        "text": "9"
      },
      {
        "data": 2.9,
        "text": "2.9"
      }
    ],
    [
      {
        "data": 6.4,
        "text": "6.4"
      },
      {
        "data": 7,
        "text": "7"
      },
      {
        "data": 3.2,
        "text": "3.2"
      }
    ],
    [
      {
        "data": 6.5,
        "text": "6.5"
      },
      {
        "data": 5,
        "text": "5"
      },
      {
        "data": 3.3,
        "text": "3.3"
      }
    ],
    [
      {
        "data": 6.6,
        "text": "6.6"
      },
      {
        "data": 2,
        "text": "2"
      },
      {
        "data": 3.5999999999999996,
        "text": "3.6"
      }
    ],
    [
      {
        "data": 6.7,
        "text": "6.7"
      },
      {
        "data": 8,
        "text": "8"
      },
      {
        "data": 3.4000000000000004,
        "text": "3.4"
      }
    ],
    [
      {
        "data": 6.8,
        "text": "6.8"
      },
      {
        "data": 3,
        "text": "3"
      },
      {
        "data": 3.5999999999999996,
        "text": "3.6"
      }
    ],
    [
      {
        "data": 6.9,
        "text": "6.9"
      },
      {
        "data": 4,
        "text": "4"
      },
      {
        "data": 3.7,
        "text": "3.7"
      }
    ],
    [
      {
        "data": 7,
        "text": "7"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 3.8,
        "text": "3.8"
      }
    ],
    [
      {
        "data": 7.1,
        "text": "7.1"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 4.1,
        "text": "4.1"
      }
    ],
    [
      {
        "data": 7.2,
        "text": "7.2"
      },
      {
        "data": 3,
        "text": "3"
      },
      {
        "data": 3.6,
        "text": "3.6"
      }
    ],
    [
      {
        "data": 7.3,
        "text": "7.3"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 4.4,
        "text": "4.4"
      }
    ],
    [
      {
        "data": 7.4,
        "text": "7.4"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 4.6000000000000005,
        "text": "4.6"
      }
    ],
    [
      {
        "data": 7.6,
        "text": "7.6"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 4.6,
        "text": "4.6"
      }
    ],
    [
      {
        "data": 7.7,
        "text": "7.7"
      },
      {
        "data": 4,
        "text": "4"
      },
      {
        "data": 3.9000000000000004,
        "text": "3.9"
      }
    ],
    [
      {
        "data": 7.9,
        "text": "7.9"
      },
      {
        "data": 1,
        "text": "1"
      },
      {
        "data": 4.1000000000000005,
        "text": "4.1"
      }
    ]
  ]
}

The json body attached is the response of JAQL query. the third column is retrieved by apply the formula: "(1* MAX([sepal_length])' )+ (-1* MAX([sepal_width])) + 0".

now I want to do some operation to the third column, filter, sort in ascending order.... not sure how to do that