Question

# Daily average - Custom measure

• 9 replies
• 1884 views

• Member
• 3 replies

Hi there,

I am fairly new to Looker but my skills in SQL are advanced. I want to achieve a Look that will give me a daily average (the average of row counts per day). The SQL query goes like this.

``````     grouping_col_1,
grouping_col_2,
avg(num_ids) as avg_daily_num_ids,
FROM (
SELECT
DATE(date) AS date,
grouping_col_1,
grouping_col_2,
COUNT(DISTINCT id) AS num_ids
FROM
``````

``````             TABLE`
``````

``````      GROUP BY 1,2,3
``````

) Q

GROUP BY 1,2```

Is it possible to build this in Looker Development Mode? How would I build a custom measure to achieve this?

Thanks

### 9 replies

OH! So that’s what @swhite was saying! Lol Long day/week for me I guess. I was thinking calculation = new measure in view. But no! I get it now. Thanks all for being patient with me!

Userlevel 3

Hey @_Noah, if you are working with a measure and a table calculation to plot on your visualization you will not be able to change the order of the columns directly as these are from different types of columns.

We can only swap order for columns of the same type (dimension to dimension, measures to measures, and table calcs with table calcs)

So in this case if you make an additional table calc that gets the values from your measure (and hide your measure) you can swap the order as you want!

So you’re saying I can move my table calculation to the left of my measure? Because it does not let me move it.

Userlevel 2

Hi @_Noah

The solution to your issue would be exactly same as was answered in this_post

1. to bring the line chart to the front of the visualization you want to move the field for the line series to the left of the field for the column series in the Data Panel. Looker renders the leftmost series in the Data Table as the ‘top’ visualization.

2. Similarly, reference lines are based on the leftmost or ‘top’ series in the Data Panel. You can move this series to the right axis in the Y panel.

If trying to move the Table Calculation to the left of a dimension. If that’s the case you can create a Calculation that just returns the value of the dimension, and you should be able to move the two columns around each other. Then you can hide the original dimension from the visualization.

Should you have any further questions feel free to email a screenshot or link to help.looker.com.

My issue is the same as what’s going on with joe’s second y-axis (the line) (see link in my previous post). You’ll notice it’s behind the first y-axis columns. I understand why it’s happening. I am looking for a workaround at this point.

Userlevel 3

Hey @_Noah,

I’m not sure I understand the issue. Regardless of whether you use one axis or many, and which axis you put on which side, the measure on the left is still going to be in the front of the visualization. Maybe a picture would help. You’re welcome to send it to help.looker.com!

I have gone ahead with the table calculation although I’m running into another blocker. What happens when we are aiming to have a daily average (line) chart on our second Y-Axis (right axis) in front of the first Y-Axis measure(s)? I am running into @swhite 's 2nd response item) now. Any suggestions with the latest and greatest in Looker features?

If not, I guess I’ll do a derived query view by the resultset dimension and try and join that into the model.

I see. It is very helpful to know that this is probably not possible to do in Looker without creating intermediate tables to achieve this. Very helpful, thanks

Userlevel 6

Yes, that isn’t an easy thing to model in LookML. You can do a lot with table calculations that can get you most of the way there…