Concatenating multiple fields into a single value result set

This is a technique for creating single value results with multiple measures.

Instead of making a single query using filters and a single measure or dimension and a single value visualization for each result, return an entire row with every single value desired:

Now from this single query, we can create a single value element that contains multiple measures of data by hiding the dimensions and measures and concatenating them, be sure to hide the fields after concatenating.

This will reduce the load time used in running parallel queries on the database which uses the same WHERE filters. It can also help when designing dashboards to reduce overall tiles by consolidating similar metrics.

3 Likes

Great little tip, thanks!

I assume this only works if the values have a hard coded format in the DB?

When I try to create a similar visual the numerical values do not retain their default display formatting.

Is there any way around this?

Hi Chris,

You may assign value_format or value_format_name to dimensions to format Looker cells using built-in or custom format names. Once those are applied in the view file, they should appear on the front-end.

Cheers,

Sami

This might be me but I can’t find the actual solution of how to concatenate different dimensions in a Calculation? :slight_smile:

Hi Woody,

Thanks for the question!

So, to concatenate dimensions in LookML, we use the ‘concat’ function and do something like this;

concat(${table_day},"-", ${table_month}),"-",${table_year}) ;; resulting in a single table that shows the full date as one.

Check out this Looker documentation on all of our different functions andon here you will see info on ‘CONCAT’. The doc gives you a nice breakdown of how to use different functions within Looker.

Also, if you have any more specific details you can reach out to us at help.looker.com if you prefer :slight_smile:

Thanks,
Jay

Thanks Jay!

Hi, this trick does not seem to work for me. Both the dimension and measure derived from it have value_format_name: eur_0 in my view file, but in the Look it just prints the unformatted value.
Sure this would work all the time?

Interesting. Could you try and test if the value formats are working at all? Maybe try percent_0 or usd_0, or even decimal_0 to see if any of the formatting is working. If those work but eur_0 doesn’t, then maybe it’s a problem with that specific format_name. If none work, I bet there’s something iffy with the way your dimension is defined.

Pasting in your dimension definition here could be helpful!

We are trying to get it working as well, looks like in 6.8 this trick is not working.

Here is a formula:

And view file with specific measure:

I’m a bit confused, tried with value_format_name: usd and with value_format: “$0.##” - nothing works as it shown in a screen.

Ah, right, I hadn’t realized the first question was regarding a table calculation. I think this is not new as of 6.8, it’s always been the case that when you concatenate in a table calculation, you’ll lose the “value formatting” from the LookML as it’s just rendering everything as a string (and not a number, or currency, etc.).

In your case, since you’re already concatenating, you could add commas and dollar signs as strings in the concat() concat("Costs: ","$",${expenses.expenses}) etc. It gets complicated quickly, though, as you’d have to use round and such to exactly replicate the value formatting.

This is something that would be good to open a request for in the feature requests section!
https://discourse.looker.com/c/feature-requests