Max as a measure

[dphigravity]

Hi,

I have a dataset where I want to aggregate a count by max. For example:

Field1 Field2 Count
A. B 10
A C 8
A D 2

Return

Field1 Max(Count)
A 10

But it does not seem like I can create a new LookML Measure as I get messages of either not finding the field in the View or that I cannot aggregate of another aggregate. Could you provide some suggestions?

[CorySchultze]

I think your best approach is to model your view as a derived table, then add a column in your SQL query a bit like this:

SELECT *, COUNT(Field2) OVER (PARTITION BY Field1) AS Field2Count
FROM Table1

Then create your measure as type: max.
Hope this helps.