Ad-hoc Metric AQL

Ask the Community
1

Hello - I am trying to create a very basic ad-hoc metric with AQL. I am trying to divide two simple dimensions, but I am getting the error posted below.

Each dimension is modeled in a format like this:

dimension demo_1 {
label: ‘Demo Value1’
type: ‘number’
definition: @sql {{ #SOURCE.demo_example1 }} ;;
format: ‘#,###’
}

AQL like:
inventory.demo_1/inventory.demo_2

Error message:

AQL Compilation Errors:
Invalid expression. Cannot reference non grouped dimension `inventory.demo_1` since it may refers to multiple values
inventory.available_quantity

Full Explore Query: ...

Is this a bug? For now, I will try making the desired ratio a permanent dimension in the model.

1 Like
2

Hi @Becca_Perry

Thanks for the question. I’m Thomas from Holistics team. Would you able to share with me (or message me privately) more information (screenshots of your AQL & error), the dimensions/metrics you are using in the VizSettings and in the AQL expression?

3

Thanks for getting back. I think the issue is because your AQL expression contains inventory.available_quantity that is not explicitly dragged into the Table Fields section. Can you go ahead and do that to see if solve the issue?

Screenshot 2024-08-30 at 11.13.24 AM
Screenshot 2024-08-30 at 11.13.24 AM614×654 19.7 KB

If you prefer not to have the inventory.available_quantity dimension explicitly in your viz/exploration, can you try to create an ad-hoc dimension with the same formula to see if it work?

aql dim

Note that if you’re in a Canvas Dashboard, ad-hoc metrics won’t be persisted to models (that’s why we distinguish between ad-hoc metrics and regular metrics). If users explore the report though, then they will see that metric, which is expected if the report uses the metric.

1 Like
4

Thank you! I struggled with the ad hoc dimensions because I would add them but then not see them anywhere. My colleague pointed out that I would be able to see them if I toggled the setting next to the search bar. Solved it! Now I can make ad hoc measures and dimensions!
image

1 Like
5

Thanks for letting us know about this! If you create an ad-hoc dimension/metric in a Reporting dataset, you’ll see that you’ll be navigated to the default dataset view automatically where you can see the newly created ad-hoc dim/metric. It seems like you are creating ad-hoc dims/metrics within Reporting reports, and there’s an issue with this flow at the moment. Let us take a look and get back to you!

1 Like