Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Daily average - Custom measure

  • 9 March 2017
  • 9 replies
  • 1949 views
S

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

lloydtabb
Rank
Userlevel 6
Badge

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…



S

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

_Noah

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.

Morgan
Rank
Userlevel 3
Badge

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!

_Noah

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.

Aleksandrs_Vederniko
Rank
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.

_Noah

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

romain.ducarrouge
Rank
Userlevel 3
Badge

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!

_Noah

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!

Reply


Terms of Service