LinkedIn
Twitter
Hello there Looker community!
I’ve been building bullet-like graphs for quite a lot of clients so I thought I’d share the knowledge here! The goal is to be able to display an actual number versus a target and a pace. It usually is used with revenue/sales numbers.
This is the end goal:
Prerequisite
- An actual number for a given month which should be a measure that will be defined as
${target}in this tutorial - A target number for a given month that can be built in a measure or a table calculation
${sales}in this tutorial
Modeling the data
You are going to build 5 Table Calculations:
-
Expected Pace
code for copy:
(${target}/extract_days(add_days(-1, date(extract_years(add_months(1, now())), extract_months(add_months(1, now())), 1))))*extract_days(now())
This gives us the expected revenue/sales where we should be at for the current day of the current month in a $ amount).
That field won’t be used in our visualization.
- % of Target
(excuse the red dot, for the sake of the example, I’ve renamed the
sales measure)
code for copy:
${sales}/${target}
This gives us the current percentage of completion towards our target (percentage)
That field will be used in our visualization.
- Pace
There we’ll start referencing the above calculations
(excuse the red dot, for the sake of the example, I’ve renamed the
sales measure)
code for copy:
(${sales}-${expected_pace})/${expected_pace}
This gives us a the percentage value of where we stand compared to the Pace. If that value is negative we’re behind, if the value is positive then we’re ahead!
That field won’t be used in our visualization.
- Expected Pace Percentage
code for copy:
if(((${expected_pace}/${target})-${of_target}) > 0, ((${expected_pace}/${target})-${of_target}), null)
This gives us the percentage needed to match the pace compared the actual sales or a null value if we’re ahead of the pace.
That field will be used in our visualization.
- Remaining
And finally:
code for copy:
if(${pace}<0, 1-(${expected_pace_percentage}+${of_target}),if(1-${of_target}<0,null,1-${of_target}))
This gives us the percentage remaining to match the target based on the pace (if our sales are behind it) or the sales (if they are ahead of the pace).
That field will be used in our visualization.
Once everything is set you should have something like that:
Building the visualization
Now that we have our data, let’s start visualizing it!
If you select the bar chart, you should have something that will look like this:
That is obviously not good, so let’s start hiding series.
-> In the data panel, hide all the fields but those three Table Calculations:
- % of Target
- Expected Pace Percentage
- Remaining
You should now have this:
Now let’s stack the series and display the value labels:
Now we’re getting there!
So now let"s change the series color so that we have a smoother transition between our current and our pace:
Almost there!
Now we want to hide the value labels for the Expected Pace Percentage and Remaining. Looker doesn’t have that feature, so we’ll bypass it by…
…changing the font colors with the corresponding colors of the series!
And while we’re at it, let’s increase the font size!
Well there we ar…
Wait… I can see that the value label for our “Expected Pace Percentage” is overlapping and we can see it… Hmm being the analytics professional that we are, we can’t allow that.
Again, Looker does not offer the feature to hide labels so let’s use the good old value format trick!
In the Value Format input that string:
#.00%[>0.10];#%[<0.10];[<0.06]
This will hide value below 6% and display values between 6% and 10% without decimals.
Now final touch!
Let’s change the series labels to match our original screenshot back at the beginning:
There you go!!
I Hope that’ll help someone!
Hi @NataschaG2!
Thanks for your question! Following up from MTLA on this one on Cyril’s behalf. This could work to track quarterly targets but a few tweaks will be required to make it happen.
We’ll have a few new fields to add to the data table before we can leverage the above calculations. For a reference of what we’re looking to obtain, here’s our end goal:
I’ve updated our date filed to ‘in the past 1 quarter’ and pulled in our created_date as we’ll need to leverage a day by day breakdown in order to create our day_of_quarter metric. Ensure that you are seeing all dates included in the quarter (even those in future with no sales) so that we can calculate the total number of days in the quarter. You may need to click the cogs wheel on the created_date field and select ‘fill in missing dates’ if you are not seeing any future dates by default. From here, we’ll add the month and day_of_month columns, respectively, to start:
extract_months(${order_items.created_date})extract_days(${order_items.created_date})We’ll reference these two columns in our third table calculation for the day_of_quarter:
if(${month} = 2, ${day_of_month} + 31,
if(${month} = 3, ${day_of_month} + 31 + if(extract_years(${order_items.created_date}) = 2024,29,28),
if(${month} = 5, ${day_of_month} + 31,
if(${month} = 6, ${day_of_month} + 30 + 31,
if(${month} = 8, ${day_of_month} + 31,
if(${month} = 9, ${day_of_month} + 31 + 30,
if(${month} = 11, ${day_of_month} + 31,
if(${month} = 12, ${day_of_month} + 31 + 30, ${day_of_month}))))))))* Note that we are provisioning for 2024 being a leap year here.
Next we’ll add in our quarterly target calculation as we have above - just entering the number itself into the expression field, adding a title and preferred format.
We’ll update the expected_pace calculation to point to our day_of_quarter calc created above:
(${target} / max(${day_of_quarter})) *
if(${order_items.created_date}=trunc_days(now()),${day_of_quarter},null)This formula will allow us to obtain a daily target by dividing the total number of days in the quarter by the total target, and then providing the pacing estimate by multiplying that daily target by the total number of days elapsed in the quarter. This calculation will also only return results for the current day’s row (as per the if statement included) which allows us to use this column to sort which will be helpful for our final visualization formatting.
Moving on to the %_of_target - You’ll want to ensure that you are generating totals for your data table here as well want to reference the sum of the total_sale_price for all days passed, divided by the full quarter target:
${order_items.total_sale_price:total}/${target}The pace calculation will remain the same as noted above, just ensuring that your total_sale_price is pointing to the total:
(${order_items.total_sale_price:total}-${expected_pace}) / ${expected_pace}
Your expected_pace calculation will also remain the same, just confirming that the table calculation names that you’re referencing are all consistent:
if(((${expected_pace}/${target})-${of_target}) > 0, ((${expected_pace}/${target})-${of_target}), null)
And finally! We’ll confirm that all our table names are still accurate for the remaining calculation:
if(${pace}<0, 1-(${expected_pace_percentage}+${of_target}),if(1-${of_target}<0,null,1-${of_target}))
Moving on to cleaning up the visualization! As mentioned above, we’re using our expected_pace calculation to sort here, which will allow us to use the current’s day row for purposes of the bullet graph. We’ll go ahead and limit our displayed rows in the plot tab of the viz settings so that we’re only returning today’s row:
We’ll then follow the same steps as above to format the bullet graph to show a) where we are, b) where we should be and c) what’s remaining:
Hopefully that helps solve your use case - let me know if you still have any questions!
