Hello, I have some additional questions I wanted to ask if possible. Thanks in advance! 1. In a table visualizations (not a dashboard), would it be possible to collapse / expand rows? Customer would like to have one row with summary data on top, and then be able to expand the visualization to see the detailed values only on expand action. What we suggested so far is just to aggregate at row level to see the summary row. I don't see a clear option to do collapse / expand of rows and I can't find reference in the documentation, so I wanted to check this with you. 2. We are stuck on achieving the following visualization: goal is to work out the actual & average days to complete a course by a learner. The closest I believe I have gotten is using the below metric, but once this is brought into any visualization, I am unable to see any other attributes. Do you have any thought on what could be the cause? SELECT AVG(DATETIME_DIFF({label/hire_date.day},{label/happened_at.day} ))BY {dataset/activities}WHERE {label/content.name}= "x"OR {label/content.name}= "y"

Hi Raffaella, 1. It’s not possible to expand/collapse rows in a Table visualization, no. But, given your explanation of the use case, you might be able to elegantly achieve a similar result by using the Drilling Feature. E.g.: you could build both a simple summary visualization and a more complete one, adding only the summary to the Dashboard. Then, via Drilling, interacting with the summary will lead the user to the more detailed insight, if they so desire. 2. By being unable to see other attributes, do you mean that they disappear from the available options in the Analytical Designer? If so, I suspect it might be related to the BY clause. Have you tried applying it prior to the aggregation (the AVG)? Something like:

SELECT AVG(DATETIME_DIFF({label/hire_date.day},{label/happened_at.day} )BY {dataset/activities}) WHERE {label/content.name}= "x"OR {label/content.name}= "y"

(note I just moved the BY one level “lower”)

Hi @Francisco Antunes, thanks so much. 1. I assumed this was the case and wanted to confirm. Totally clear now, thanks 2. Yes, correct - they are hidden once that metric is added to a visualization. I am going to try again with your suggestion applied. Thanks!

Sorry, you’re right! The aggregation was done correctly in the original Metric. Let me test on my end and I’ll get back to you soon 🙂

Thank you 🙂

I think I have figured it out! I was actually thinking correctly in my first reply, but I had the wrong syntax; We were missing another SELECT for the DATETIME_DIFF:

SELECT AVG(SELECT DATETIME_DIFF({label/hire_date.day},{label/happened_at.day} )BY {dataset/activities}) WHERE {label/content.name}= "x"OR {label/content.name}= "y"

In my tests this did not restrict the available attributes once the metric was inserted to an insight in the Analytical Designer. Would you kindly try it out?

I've tested it and it works fine! Thanks for spotting the issue and proposing a solution, much appreciated!!!

It’s my pleasure! Glad it worked out 🙂