Re: Table calculations of totals and row totals?

herdrick · 06-21-2017 07:59 PM

Question:

In past I know that table calculations weren’t allowed for totals. See [RETIRED]Why don't my table calculations have totals? .

I don’t quite see why table calculations couldn’t simply work on total rows in the same way they work on regular rows. Is it because some table calculation formulas would work on regular rows but return misleading results on total rows? I could see the mixing of null and non-null values being tricky for calculations using mean() and others, but SQL has the same issues and deals with them. Admittedly, sometimes the way SQL deals with them is very confusing to users and I could imagine that Looker would prefer to err on the side of caution.

Or has this changed?

Thanks!

William_Lane

Hiya Ethan,

See my answer in Why don't my table calculations have totals?.

Was that answer what you were looking for?

Thanks,
William

herdrick

@William_Lane No, what I’m asking is this: if you click ‘Totals’ in the UI, you get an additional row on the bottom, the totals row. So, why can’t a table calculation (I’m thinking here of a table calculation which doesn’t use any “:total” fields) operate on that row in the same way it operates on every other row?

Alex_Hancock

Hey Ethan - I think it’s because measures are not guaranteed to be additive or follow line behaviour at the totals row. However i do agree it would be great if there were an option to ‘apply on total row’ (with a warning) so that the same calculation could be applied at the total row level.

Jiro

Hi Ethan,

This is great feedback for the product team and clearly from Alex’s comment you aren’t the only one who is interested in this topic. Building a little on Alex’s and William’s comments here is some more background on what our product team is facing in this area:

Since row totals are a different series in and of themselves, we can’t have table calcs necessarily “continue” on to apply to the totals row without causing quite a bit of confusion. i.e is that value a total of the table calc column or is it the table calc applied to the row totals? In most cases these would be very different numbers

ceik

Was also just looking for this and surprised it’s not possible. I can see the challenges with regards to implementation, however I think this is a pretty essential feature with some very basic use-cases. There should be an option that lets me chose how the table calculation should be computed for the totals and they should probably be turned off by default.

Aleksandrs_Vede

Hey @ceik,

We appreciate the feedback. This feature has been on our Product team radar for some time. The challenge here is that totals mean two different things. We can’t do a unique total with a table calc, so we’d have a lack of parallelism between the two totals which feels problematic to us. However it is something our Engineering team is looking into. Hope this clarifies it for you.

Regards,

Sasha

Britt1

Adding my vote that this would be a very useful feature.

Patricio_Munar1

+1 for this feature.

Parker_Tenpas

+1 for this.

Are there any updates on progress / work-arounds / or other fixes on this?

bcorwin

+1 for this as well

Andrew_Tarvis

+1 from me as well. Do we have any updates from the Engineering team since July of last year? Thanks!

Morgan1

Hi Andrew,
No updates from engineering/product but all of the +1s here are being considered. Thanks for checking in!

jakeshi

+1 would want to have this.

kpmartin87

+1 as well

jkassof

+1 here as well, this is a very commonly requested capability

JohnRomanski

+4 from WB/TBS as well, (I am building dashboards for four separate clients so far, and three ask for this everytime they see a table without total calculations, and one is about to get their first table without total calculations.)

JohnRomanski

One other approach is to take the values out of the table calculations and put them into the base view. I was previously doing some table calculations to determine Week over Week growth by offsetting rows, etc.

To get around the limitations of the table calculations though, I have added the last week numbers inline with the current numbers, and then added the percentages to the view. These percentages get calculated across all the rows including the cumulative total rows.

  measure: weekly_sub_growth {
    type:  number
    sql: (${total_paid_subscriptions}::numeric/${total_lastweek_subscriptions})-1.00 ;;
    value_format_name: percent_1
  }

There are often ways to work around the limitations of Looker if you take the time to materialize the data as you need it displayed.

desiree

Hi John- looks like you’ve made a good use case here! I’ll also add your previous +4 to the product team’s list for this request.

Cheers.

Mason1

+1

this is an essential feature and hope to have it available soon

jbroberg

+1 from me
+2 from my engineers who wish Looker had this feature
+3 from my analysts who avoid using Looker because it lacks features like this

desiree

Thanks, @jbroberg, I’ve passed along your feedback to the Product team. And thank you for the details on who in your company is interested in this feature.

menashe

I’ll add my +1 to the pile.

maxgsoulcycle

+1. Analysts here ask about this regularly.

Alexr1

Adding a +1 in hopes this gets prioritized

Greg_Roberts

+1 here.

Astonishing that this has been open so long. Seems like a very small thing to implement.

ari_sigal

+1 for this feature

gavin_wims

Just an update on this, folks - the Product team is still actively considering adding table calc totals and they are well aware of the level of demand that exists for this feature. It might be worth checking back with us in a few weeks as there may be a positive update by that time.

Ezra_Wolfe

+1 on this 😎

leticia_esparza

Hey @Ezra_Wolfe,

I was sure to relay the feedback to the product team. Thanks for letting us know that this is something you’d like to see!

Cheers,

Leticia

marieb

As of Looker 6.2, table calculations now work with totals. See this documentation page for more information.

slacoul

@marieb does the table calc have to be in a certain format/measure? I don’t see the feature working as expected (testing Looker 6.4)

izzymiller

What about it don’t you see working? The thing to remember with totals is that they run the same function as the column, but only over the totals row— So a mean() table calculation will return mean(total), not the sum of the values in that column.

Something like stdev() for example, which requires multiple values, will return 0, since it’s trying to execute over just the totals row/1 value. Does that click with what you’re seeing?

slacoul

I created a table calc that is an integer multiple of a column in the results and expected the sum of the individual values in that column. from your response seems this should’ve still worked since integer*(sum of values) = sum of (values*integer), however I’m seeing a NULL result.

Perhaps, when no functional operators are used there should be a default behavior?

izzymiller

That use case does sound like it ought to work… I just tried 5.0*${table.count}and it’s correctly doing 5*${table.count:total} for the totals row. What’s the full formula you’re using?

The default behavior idea is not a bad thought— Like for your example, then by default it would be just a sum total (what most people think of when they think total). I’ll bring that up internally.

The question is, does it get too confusing to have different behaviors for the same feature? It’s hard to message that properly with a good UI/UX…

slacoul

Ah, I see the issue. The Look I’m debugging is performing a dimension * integer (the dimension is a type: number)

I was able to work around this by:

didn’t work: created a table calc referencing the dimension, then another table calc referencing this. seems this produces a table calc that behaves like a dimension too for this feature.
worked: created a sum custom measure referencing the dimension, then created a table calc referencing this custom measure which accurately produced the sum of dimension * integer I needed

izzymiller

Aaah, that makes sense. Nice job!

jwhitfield

Any update on this? Not able to see any Table Calc totals

barrow2013

any updates on this function? I really need this to work.

Isaac1

So if I’m reading @izzymiller’s comment correctly, the total of a table calculation column isn’t a sum of the column but rather applies the same calculation against the measure’s total? If so, i’m surprised this isn’t a feature yet… there should be an option to have the table calculation column’s total sum the values within its column.

For example, I have a daily measure of orders and a table calc of the delta vs. the prior day. I’d like to see what the total delta for the period is.

offset(${orders_daily.total_orders}, -1) - ${orders_daily.total_orders}