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.
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.
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:
- Is there anyway I can force looker to not use aliasing when doing a group by?
- 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! =)