Calculate the frequency of duplicates using table calculations in Looker

Question

I have an explore like the following -

Timestamp Rate Count
July 1   $2.00  15
July 2   $2.00  12
July 3   $3.00  20
July 4   $3.00  25
July 5   $2.00  10

I want to get the below results -

Rate Number of days  Count
$2.00      3          37
$3.00      2          45

How can I calculate the Number of days column in the the table calculation? I don't want the timestamp to be included in the final table.

Answer 1

First of all— is rate a dimension? If so, and you have LookML access, you could create a "Count Days" measure that's just a simple count, and then return Rate, Count Days, and Count. That would be really simple.

If you can't do that, this hard to do with just a table calculation, since what you're asking for is to change the grouping of the data. Generally, that's something that's only possible in SQL or LookML, where you can actually alter the grouping and aggregation of the data.

With Table Calculations, you can make operations on the data that's been returned by the query, but you can't change the grouping or aggregation of it— So the issue becomes that it's quite difficult to take 3 rows and then use a table calculation to represent those as 1 row.

I'd recommend taking this to the LookML or SQL if you have developer access or can ask someone who does. If you can't do that, then I'd suggest you look at this thread: https://discourse.looker.com/t/creating-a-window-function-inside-a-table-calculation-custom-measure/16973 which explains how to do these kinds of functions in table calculations. It's a bit complex, though.

Once you've done the calculation, you'd want to use the Hide No's from Visualization feature to remove the rows you aren't interested in.