Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Add Static Value within a Look (Table Calculation)

  • 2 February 2018
  • 3 replies
  • 1843 views
P

Hello, sounds silly, but I’m looking how to add a static value inside the custom table calculation field.



The reason is so that I can show the % change within the “Single Value” Visualization field. I’m trying to calculate difference between current value and the benchmark number.





Can anyone recommend a simple way to do this?



Thank you!

3 replies

P
Rank
Userlevel 1

To add a static value into your table calculation you will need to hard code it into the calc field. For example, to add your benchmark you would type the value of the benchmark into the field and that is it. The table calc should look like this:





This should put your static benchmark as the comparison field.



Your visualization should then look like this:





Just make sure your table calc is the field right next to your percent change to ensure it is the field being compared.



Here is a great Looker Doc on single value visualizations:





P

Thanks Peter, I’ve managed to figure out how to add this, but my issue was getting “60” rather than “600”. It seems that however I added it, it never appeared as “60”. I looked at the Single Value Chart Options doc and added a custom format - 0.00%, and ended up getting 60%.



But I’m still have trouble calculating the percent change and I’m kinda stuck.





  1. I already have my Metric added


  2. I added a custom calculation for benchmark.


  3. Now I’m trying to calculate the percent difference.


    My formula is: (${viewability_benchmark}-${nw_media_plan_fact.Viewability})/${viewability_benchmark}




See below





But I’m doing something wrong because I keep getting this:





The difference should be 4.5% but I can’t seem to get this.



Would you be able to advise what I’m not doing right? Thanks so much.

P
Rank
Userlevel 1

When using a table calculation the variables being calculated will represent their actual underlying values and not their formatted values. So in your case, its possible that the variable: ${viewability_benchmark} is actually 60 and ${nw_media_plan_fact.Viewability}: could be for example .62. If you change your Viewability benchmark to .60 then it should work out.



One more thing, I believe your % Change formula should be :


(${nw_media_plan_fact.Viewability} - ${viewability_benchmark})/${viewability_benchmark}



(New Value-Old Value)/Old Value = % change



Hope this helps! If you have any more questions you can always visit help.looker.com or jump on chat.

Reply


Terms of Service