I have a custom dimension called “Day” and I want to create a table where I list each day in the first row (i.e. Mon, Tue, Wed, Thu, Fri, Sat, Sun) and in the second column, I want to count how many times each day occurred. So, the output is like “Mon: 401, Tue: 500” etc.
How do I do this in a calculated field, if possible?
I answered this over on the Looker Community but I'll paste it here too, for visibility.
This is actually pretty hard to do in a calculated field!
Table calcs operate over the result set after it’s been returned— So the grouping that you get there is the grouping that a table calc would operate over. Returning a dimension and a count table calc/custom measure would only count the visible rows, and since SQL has grouped all of the similar rows together, you’ll get a result of 1, every time.
A lookml measure of type count, however, will do this correctly, since it’ll calculate the count at the same time as the other results are being generated. Here’s an example: A date field, a custom measure of type count on that field, and then an actual measure of type count. You can see that the custom measure returns 1 every time, while the LookML measure returns the actual count.
I’d go for that! It’s one of the easiest LookML fields to implement, actually. You just need to say
measure: count { type: count } and that’s literally it. For more: https://docs.looker.com/reference/field-reference/measure-type-reference#count
