Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Table totals not adding up for hardcoded values

GenDemo
Userlevel 4

Why is table totals not adding up for hardcoded values?


image

17 replies

izzy
Userlevel 7
Badge +1

What do you mean by “hardcoded values” here? I bet the answer lies in the SQL being executed. If you check out the SQL tab on that explore (maybe simplify the explore down to be just the Collection Officer Target field for readability), what’s the totals query look like?


It should show as a separate query following a comment in the SQL.

GenDemo
Userlevel 4

Hi Izzi


no, I mean if in your visualisation you create a table calculation and hardcode the value. Then those values dont add up in a table. The total is either missing or shows zero (as in my screen shot above).

Cyril_MTL_Analytics
Userlevel 5
Badge

it’s probably because looker consider your hardcoded value as a dimension and not a measure, hence not computing the total.


To correct that make it a sum.

GenDemo
Userlevel 4

hmmm…


so what I have is this:


if(${Officer.name} = “Louie”, 420000,

if(${Officer.name} = “Henry”, 300000,

if(${Officer.name} = “Caitlin”, 420000,

if(${Officer.name} = “Lauryn”, 420000,

if(${Officer.name} = “Hayley”, 420000, 0)))))


so I’ve tried

if(${Officer.name} = “Louie”, sum(420000),

if(${Officer.name} = “Henry”, sum(300000),

if(${Officer.name} = “Caitlin”, sum(420000),

if(${Officer.name} = “Lauryn”, sum(420000),

if(${Officer.name} = “Hayley”, sum(420000), sum(0))))))


but that gives me some really wonky results.

Cyril_MTL_Analytics
Userlevel 5
Badge

Hmm, sorry I honestly thought that would fix the problem but it seems that only table calc based on measures can have a total. Table calc based on dimensions (or hard coded values for that matter) won’t show in a row total.

A

@GenDemo @Cyril_MTL_Analytics A workaround for this might be to just perform a sum in a separate table calculation column - not sure if that would work for what you’re specifically looking to do but it is a way of pulling out a total.


Thanks, Adam

A

Funny enough @izzy I’ve just looked at the SQL generated by the example I ran (with one dimension and an if statement in a calculation column generating numerical values) and there was no mention of a total query in the SQL - could this be because there are no measures being referenced?


And (I’m no expert so might be way off but) would I then be right in thinking adding table calcs don’t actually change the SQL, they just apply to the visualisation, so in this case if @GenDemo has a query with a dimension and then a table calc column with an if statement, there wouldn’t be any values for the SQL to run a sum query on (as there are no measures, and therefore nothing to affect the SQL in this sense)?

Cyril_MTL_Analytics
Userlevel 5
Badge

@adstott90 that actually won’t work as I think Looker still considers that table calc as a dimension since it’s based on a dimension and not a measure.

GenDemo
Userlevel 4

This might works, but it doesnt look nice. My business users dont like it.

They just want the total at the bottom.

GenDemo
Userlevel 4

and I dont want to put this in LookML, as I want the team lead to be able to edit the tile and change the values.

A

@Cyril_MTL_Analytics It wouldn’t work in terms of pulling out a column total, it just displays it in a separate table calc column - the values being based on dimensions rather than measures doesn’t actually seem to affect anything to be fair.


Thanks, Adam

Cyril_MTL_Analytics
Userlevel 5
Badge

You’re right @adstott90! Actually when based on a dimension, the total row of a table calc is empty but when based on a measure, it just computes the table calc formula to the row total of the measure (and not a sum of the row values which somewhat makes sense)

izzy
Userlevel 7
Badge +1

You are all correct, here!


Table calculations execute in the frontend, in javascript-- Which is why you can do some crazy cool stuff with them. They do not appear in the SQL (there are a few specific exceptions) so I was totally off-base with my advice to check the SQL. I didn’t know it was a table calc.


There is a workaround here. Cyril is correct that there are “Dimension table calcs” and “Measure table calcs” that have different behavior. The key here is that we need to trick Looker into thinking that your hardcoded dimension table calc is a measure table calc, and it’s pretty easy to do that.



  1. Add a measure to your explore (any measure will do, it doesn’t matter. Maybe something performant 🙂 )

  2. Write your table calculation as `hardcodedvalue + (${view.dummy_measure}*0)

  3. Save, and observe your total! You’ll want to select “Hide from Visualization” on the dummy measure.


Simply by referencing a measure in the table calculation expression, that calc becomes a “measure table calc”-- Even if you’re just adding 0 to your hardcoded value by multiplying the measure * 0.

image

A

Thanks @izzy that makes sense! The only question now then is going back to the original post, it still doesn’t look like the column totals are adding correctly, so in terms of that, the question still remains.


Thanks, Adam

GenDemo
Userlevel 4

I’ve also tried to add

+ (${amount}*0)

into the table calculation, but this puts that column into each of the pivot columns, and also still doesnt add up.


image


where, I want this column to only show on the left like this:


image

izzy
Userlevel 7
Badge +1

You’re completely right. I don’t think there is a way to get this to work for a “hardcoded value” given the way that we calculate totals (see: https://help.looker.com/hc/en-us/articles/360001285527-Why-Don-t-My-Totals-Match-the-Values-in-my-Table- for reference). This is a great point and something I’ll flag internally.



Ah. Ignoring the glaring issue that I just mentioned above, this is still possible I think, by adding a pivot_where() function to the table calculation. That will remove it from the pivot columns and let you move it around as needed in the new table viz. But the totals issue still stymies this one.

GenDemo
Userlevel 4

Thnx for your responses @izzy.


Yes I am aware of that “why dont my totals match the values in the table” - it is a rather annoying quirk.


Hmmm… yeah that is not going to work either really…

Reply


Terms of Service