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! Go to Solution.
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" ]
}