Sum sales between start and en date

Michaël_S
Participant I

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.

Solved Solved
0 2 1,047
1 ACCEPTED SOLUTION

David_P1
Participant V

hi @Michaël_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.

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 solution in original post

2 REPLIES 2

David_P1
Participant V

hi @Michaël_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.

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" ]

}

Michaël_S
Participant I

Thanks a lot @David_P1 & @Cyril_MTL_Analy 

Top Labels in this Space
Top Solution Authors