Solved

Sum sales between start and en date


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
Badge

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 

Reply