Sum of duplicated rows

How can we calculate the total of a table where there are different granular level of the table?

I have a table like this:

row date country show campaign view
1 2022-10-01 US first_show abc 10
2 2022-10-01 US first_show xyz 10
3 2022-10-01 AU first_show def 20
4 2022-10-01 UK second_show ghi 10

Row 1 & 2 are the same but since they have different campaign, they are split into different rows. 

I am wondering how can I calculate the total view without the duplicated rows for each show because of the campaign name. 

In SQL runner, I can achieve the calculation by :

WITH temp AS (

   SELECT

      SUM(view) AS view, show

   FROM 

      table

   GROUP BY

      show)

SELECT

   SUM(view) AS total_view

FROM
   temp

But I am not sure how to achieve this with the LookML code. Ideally it can be done in LookML side so we don’t confuse the user when they are using the explore view.

Any help on this topic is greatly appreciated! 

Thanks!

0 1 477
1 REPLY 1

Dawid
Participant V

Do you need to have Campaign field in this query? That is the field that changes your granularity.


You could try to change the “view” measure to “sum_distinct” and create a key that doesn’t take campaign into consideration - that can, however, make the interpretation of this measure confusing for other people

Top Labels in this Space
Top Solution Authors