How to calculate percentage of change based on filtered time period?

[Jaimini]

Hello!

So my question is about extracting the start and end dates of a looker filter and using them for calculations.

In my specific case I’m interested in calculating the percentage of change over whatever time period the user sets, however I can imagine this is applicable to any calculation that might require values from the start and/or end dates of a filtered period.

I have managed to do this, but it’s quite messy and I was hoping that maybe there would be a more elegant solution.

Current Strategy:

Using a derived table along with the the start_date and end_date attributes of a templated filter (described in this looker article), I’m able to capture values at the start and end dates using a double join with in looker. It looks something like this:
 

As you can see, it seems quite messy.

So I tried another idea, and almost had it work.

Attempted Strategy

This idea is based on capturing the start_date and end_date of the filter into measures (as described in this looker article ) using max and min.

So far so good.
Now the intention is to calculate the percentage of change using table calculations (specifically the offset function described in this article ).
To use this, I need to filter out all the other rows in the view, to leave just the rows that have the start_date and end_date of the filter. Then I can apply the table calculation.
To filter these, I thought I could create a yesno/boolean measure that indicates if that row’s date = start_date or end_date. Then, I can filter the rows based on this measure and then apply the table calculation.

Calculating this yesno measure is actually simple.
However, this is where it breaks.
When trying to create a view for these dimensions:

This is the sql code generated by looker:
 

And the error is generated because of the aliasing that looker uses when automatically creating the group by. When this alias is replaced by the actual column name (“.date”), the query works fine.
So if there is some way I can force Looker to not use aliasing when doing the group by, the whole thing should work.
 

So, this is the closest I’ve gotten to making this work cleanly. And my question is two fold:
​​​​​​

1. Is there anyway I can force looker to not use aliasing when doing a group by?
2. Is there a better way to capture the start_date and end_date of a filter to cleanly calculate the percentage of change?

Thank you for reading! 😃

[Jaimini]

So, we discovered a solution that seems to be relatively clean, and does not need a derived table.
 

1. Using the date_start and date_end fields of a template filter (described here),  store the dates as dimensions (didn’t know you could do that).
2. Create a yes/no(boolean) dimension to indicate if a row is the start date or not.

   

3. Use a custom filter to keep just the rows that are yes for start/end dates
4. Apply the table calculation for percentage of change (described here)