Looping Over Dimension Values

smotupal · 09-12-2022 04:47 AM

Hi,

I am trying to loop over dimension values in a measure, for example, if we have month , then I need to loop over each month value and calculate the dimension dynamically.

For each month, measure value will be changed. Is there any possibility of implementing this in Looker.

Regards,

SM

Roderick

Hi @smotupal ,

Yes, it is possible to loop over dimension values in a measure in Looker using Liquid templating language. You can use Liquid's for loop to iterate over the dimension values and calculate the measure value dynamically.

Here's an example of how you can achieve this:

{% for month in dimension_values.month %}

{% assign month_sales = 0 %}

{% for row in table %}
  {% if row.month == month %}
    {% assign month_sales = month_sales | plus: row.sales %}
  {% endif %}
{% endfor %}

{{ month }}: {{ month_sales }}

{% endfor %}

In the above example, we are looping over the month dimension values using the for loop. Inside the loop, we are calculating the month_sales measure value by iterating over the table and summing up the sales column for each row that matches the current month.

You can modify this example according to your specific use case and calculate your measure value dynamically for each dimension value.

chapo

Thanks for sharing Roderick! Where can you use these for loops?

I'm trying to use this approach to dynamically create a case statement inside of a dimension based on potential values of another dimension.

What I'm really trying to do is create a required drop-down filter in a look of potential values of a field that exists in a column for the table/view.

Something like this:

Saurabh_Sharma

Hi @Roderick ,

I would like to know where to put the code mentioned by you. Should I put it in the SQL part of the measure or the in derived table section in the view?

Also, what is the 'table' table supposed to denote here? Is it a variable or another column?