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!
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