Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Show this months total and the average months in the same chart

  • 25 October 2018
  • 2 replies
  • 1134 views
J

I’m a complete newbie to Looker so please forgive if this is an obvious answer.



I am trying to show both the current months count of transactions and the average number of transactions in a month in the same chart broken down by customer.



I can get these two items in separate charts but want to know how to combine them into one chart.



This month


I display a count which I filter by last 1 month and pivot by customer to show me a list of customers with the number of transactions each.



Average month


I have a dimension that tells me how many months they have been a customer. I then do a count on how many transactions they have done in total with no filter on it. Then I do a table calculation to divide one by the other giving me the average transactions per month.



So the question is, how do I get them on the one chart so that I can see how many transactions they have done this month and how that compares to the monthly average on a per customer basis.

2 replies

J
Rank

Hey Jordan,



There are different ways to achieve what you are trying to do. From the setup you described I would suggest to create a new yesno dimension, which will confirm the current month and a filtered measure in the respective view.



They would look something like this:



dimension: current_month {


type: yesno


sql: EXTRACT(YEAR_MONTH FROM NOW())=EXTRACT(YEAR_MONTH FROM ${orders.created_raw}) ;;


}



measure: filtered_count {


type: count


filters: {


field: current_month


value: “Yes”


}


}



Please note that the sql for the current month might differ depending on the dialect you are using. Now you can go to the explore and select the customer dimension, customer month dimension and the two measures filtered count and count. Afterwards you can normally calculate the average transactions per month with a table calculation. This way you should now have the customer name, month as customer, their current month orders, all time orders and average orders per month in the explore.



Here is an example with cities instead of customers, to show you what it would look like:



09



Kind Regards.


Jonathan

J

Thanks for this Jonathan.

Reply


Terms of Service