Solved

# Sum sales between start and en date

• Member
• 7 replies

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.

icon

Best answer by David_P 4 May 2021, 22:30

hi @Michaël.S, check the answer provided by @Cyril_MTL_Analytics 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.

What you could do is create a `yesno` dimension and filtered measure:

``dimension: is_between_dates {  type: yesno  sql:  \${createddate_month_num} >= 3 and \${createddate_month_num} <= 6  ;;}measure: filtered_count {  type: count  filter: [is_between_dates: "Yes" ]}``

View original

### 2 replies

Userlevel 3

hi @Michaël.S, check the answer provided by @Cyril_MTL_Analytics 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.

What you could do is create a `yesno` dimension and filtered measure:

``dimension: is_between_dates {  type: yesno  sql:  \${createddate_month_num} >= 3 and \${createddate_month_num} <= 6  ;;}measure: filtered_count {  type: count  filter: [is_between_dates: "Yes" ]}``

Thanks a lot @David_P & @Cyril_MTL_Analytics