Complex issue with the way Looker calculates totals

[moebe]

Looker offers to calculate the total in addition to the details in an Explore:
 

This has always been a bit tricky, and in my opinion a suboptimal way to go.
Today, however, I encountered a problem that actually makes this path become ridiculous, and I don't know how to get around it either.
 

Here I now have the case where I only want to see the market value where the OP <> 0.
How does looker do that?

 

SELECT
         SUM ( sum_op )     AS sum_op
      ,  SUM ( marketvalue ) AS marketvalue
  WHERE  bla = bla
HAVING sum_op <> 0

Of course, this approach cannot work at all, because the sum over everything, i.e. without the elements (see screenshot 1) is not <> 0. Therefore, the total that looker calculates does not match the correct total.

The bad thing is: I don't see any model-technical way to get around this.
This is clearly a BUG for me as a customer, as the totals do not match the displayed values and my customer cannot trust the displayed totals.
With a dummy example like the one shown, the human being is still easily able to see the error.
But already with 10 lines, this is not noticeable and leads to wrong business decisions.

Hence my question:
Have you also noticed this problem and have you found solutions for it?

by the way:
this is the solution proposed by Looker!
However, it is not suitable for end users, but only for developers:
https://cloud.google.com/looker/docs/best-practices/how-to-troubleshoot-looker-totals

Thank you for your feedback and advice!
Ben

[moebe]

I thought about it again and found a - I think - simple solution.
We as users can't implement it, but it shouldn't be rocket science for Looker/Google.
 

To fix the problem with the wrong totals, Looker/Google would only have to rewrite the TOTAL statement minimally and enclose it with a CTE or a subquery / inner query.

/* Currently, Looker computes the detail values and the total using the following method: */
SELECT
          attribute_1
      ,  attribute_2 SUM ( sum_op ) AS sum_op
      ,  SUM ( marketvalue )        AS marketvalue
     WHERE
          bla = bla
     GROUP BY attribute_1
      ,  attribute_2
     HAVING
          sum_op <> 0
;
-- sql for creating the total and/or determining pivot columns
SELECT
          SUM ( sum_op )     AS sum_op
      ,  SUM ( marketvalue ) AS marketvalue
     WHERE
          bla = bla
     HAVING
          sum_op <> 0
;
FETCH NEXT 1 rows only
/* Solution  */
SELECT
          attribute_1
      ,  attribute_2 SUM ( sum_op ) AS sum_op
      ,  SUM ( marketvalue )        AS marketvalue
     WHERE
          bla = bla
     GROUP BY attribute_1
      ,  attribute_2
     HAVING
          sum_op <> 0
;
-- sql for creating the total and/or determining pivot columns
SELECT
          SUM ( sum_op )     AS sum_op
      ,  SUM ( marketvalue ) AS marketavalue
     FROM (
               SELECT
                         attribute_1
                     ,  attribute_2 SUM ( sum_op ) AS sum_op
                     ,  SUM ( marketvalue )        AS marketvalue
                    WHERE
                         bla = bla
                    GROUP BY attribute_1
                     ,  attribute_2
                    HAVING
                         sum_op <> 0
           )
;
FETCH NEXT 1 rows only

[DerChrisser]

Hey @moebe 

I encountered the same issue and found that using table calculations suggested by Google isn't viable because it limits the result set to 5000 records.

Have you found an alternative solution? Your total statement seems like it would work well, considering it incorporates all row-level logic.

Best,

Christian

[blue1]

Hi @moebe,

Your problem is that you are filtering on the sum, which results in a HAVING argument instead of a WHERE. HAVING is computed after the sum, so the total is not correct.

I suggest that you create the sum of OP and Marketvalue in a PDT. You can then use them as dimensions and filter on OP > 0. A measure will be used to display the sum of OP and Marketvalue in the data table.

view: test {
derived_table: {
  sql: SELECT * FROM (
  VALUES 
    ('ID.1', 'FLOAT', 2.64, 137110), 
    ('ID.2', 'FIXED', 0.00, 228930), 
    ('ID.3', 'FIXED', 0.00, -228930), 
    ('ID.4', 'FLOAT', 0.00, -137110)
) AS t(id, price_status, op, marketvalue);;
}

  dimension: id {
    primary_key: yes
    label: "ID"
    type: string
    sql: ${TABLE}.id;;
  }

  dimension: price_status {
    type: string
    sql: ${TABLE}.price_status;;
  }

  dimension: op {
    label: "OP"
    type: number
    sql: ${TABLE}.OP;;
  }

  dimension: marketvalue {
    type: number
    sql: ${TABLE}.marketvalue ;;
  }

  measure: sum_op {
    label: "Sum OP"
    type: sum
    sql: ${op} ;;
  }
  
  measure: sum_marketvalue {
    type: sum
    sql: ${marketvalue} ;;
  }
  
}

Here is the table unfiltered:

Here is the table filtered on OP > 0.


Hope this helps!