Looker Community

Dimension to identify an active account between two order dates

Hello,

I’m looking to create a CASE (or alternative) dimension in LookML.

I have a dimension for first order date, and last order date on an accounts table. On those I have timeframes which include the quarter.

What I want is another dimension that identifies on an orders table the quarters in between the first and last order and (including the first and last order date) so I can see all the quarters in which a customer was active… even if they didn’t purchase in a specific quarter.

Ex.

First Order Quarter = 19Q1
Last Order Quarter = 19Q3
No orders in 19Q2

I need the dimension to say “Active” (or the like) for all 3 quarters on the dimension so I can see the total number of quarters that customer was active.

Thanks!