Fx MIN GROUP BY unable to spot the expression error

[Luganega]

Good day everybody!

I'm a newbie in Looker. Currently i have created a simple Look with 3 columns.

Columns A: Vessel name / columns B: Port call / column C: date of call (all 3 fields are dimensions)

In my Look every vessel calls more than one port. 

Now I would likes to calculate in column D the Min date of call for each Vessel. 

I have already tried with: Min (date of call) over (partition by Vessel name) but It does not work... Same result with Min (date of call) group by Vessel name

Has anyone suggestion to solve this problem? Thnks

[Sharon_Z]

@Luganega You can this with the following table calculations, but you'll need to make sure that you're sorting first on vessel name and then call date ascending.

group_row_start: match(${vessel_name}, ${vessel_name})

min_date: index(${vessel_name}, ${group_start_row})

The best way to use window functions in Looker though is by defining them via derived tables in the LookML layer instead. This would ensure that you are not depending on the sort order of the fields being displayed in the Explore.

[Luganega]

Dear @Sharon_Z your suggestion works but It does not solve my issue... My finale goal Is to create the attached graph. Do you see any possibile solution?

 

[Sharon_Z]

@Luganega You could add two more table calculations to achieve this:

row_number: row() - gets you the row number of each row

matches_start_row(): if(${group_row_start}=${row_number},yes, no) - Returns Yes or No value 

On the matches_start_row column, click on the settings button and select "Hide nos from visualization". That should dedupe your repeated rows for the vis.