Sum sales between start and en date

Looker Q&A Looker
1

Hi all, hope you’re doing great.

Context

I would like to find a way to calculate Purchase Offline KPI.

This KPI is basically a sum of the offline sales realized 7 days after a visit online.

Example

Rob has visited my website the 2021/02/01 and he bought some furnitures in my store the 2021/02/03 and 2021/02/05 for a sum of 100$.

Problem

I try to find a way to realized this KPI in LookML. I create a new dimension called endate with a basic DATE_ADD(${TABLE}.startDate, INTERVAL 7 day). But I cannot see how can I run sum with a between in LookML.

Any help?

Thanks a lot.

2

hi @Michael_S , check the answer provided by @Cyril_MTL_Analy in this post. You could use the same approach of creating a boolean dimension and then adding a filtered measure - the only difference being that your SQL should include the 7-day interval logic.

1 Like
3

Thanks a lot @David_P1 & @Cyril_MTL_Analy