Hi team,
I am quite new to Looker so every input is valuable for me.
I am trying to implement period over period analysis. I am following this article https://help.looker.com/hc/en-us/articles/360050104194-Methods-for-Period-Over-Period-PoP-Analysis-i...
I have chosen simple way to start with “Any Two Native Timeframes”. I am assuming this is the easiest one.
As per article I have created dimension
dimension: wtd_only { group_label: "To-Date Filters" label: "WTD" view_label: "_PoP" type: yesno sql: (EXTRACT(DOW FROM ${created_raw}) < EXTRACT(DOW FROM GETDATE()) OR (EXTRACT(DOW FROM ${created_raw}) = EXTRACT(DOW FROM GETDATE()) AND EXTRACT(HOUR FROM ${created_raw}) < EXTRACT(HOUR FROM GETDATE())) OR (EXTRACT(DOW FROM ${created_raw}) = EXTRACT(DOW FROM GETDATE()) AND EXTRACT(HOUR FROM ${created_raw}) <= EXTRACT(HOUR FROM GETDATE()) AND EXTRACT(MINUTE FROM ${created_raw}) < EXTRACT(MINUTE FROM GETDATE()))) ;; }
and when I execute it, it returns error Query execution failed: - A valid date part name is required but found DOW at [4:28
I tried to fix it and replaced DOW by day_of_week but no luck
This is what I am trying to run:
Please help me to undertstand what I am missing?
Thanks in advance
Rohit
Solved! Go to Solution.
Hi there, I think “DOW” needs to be replaced with DAYOFWEEK. Give it a shot and let me know if it doesn’t work.
Hi there, I think “DOW” needs to be replaced with DAYOFWEEK. Give it a shot and let me know if it doesn’t work.
Hi Rking,
How I have created the date field:
dimension_group: created { type: time view_label: "_PoP" timeframes: [ raw, time, hour_of_day, date, day_of_week, day_of_week_index, day_of_month, day_of_year, week, week_of_year, month, month_name, month_num, quarter, year ] sql: ${TABLE}.created_at ;; convert_tz: no }
When I go to explorer and select filter on month field and run it:
It throws error:
I know why error is coming, _PoP Created Month has values in YYYY-MM and filter has value 3 in intiger but I am not sure why _PoP Created Month returning such value which can’t match with intiger 3 in filter?
Can you please tell me how to reformat the values in _PoP Created Month?
Hi Rohit,
Try adding one more line of code for datatype: date. I sometimes get errors using dates as filters and that usually fixes it.
dimension_group: created {
type: time
view_label: "_PoP"
timeframes: [
raw,
time,
hour_of_day,
date,
day_of_week,
day_of_week_index,
day_of_month,
month,
month_name,
month_num,
quarter,
year]
sql: ${TABLE}.created_at ;;
convert_tz: no
datatype: date
}
datatype: date sorts out this problem but the objective is to find out month over month percentage. This solution creates a chart with two lines. I need one line with this month sales/previous month sales floating over day of month.
This is how my explorer looks like:
Can you please help me to achieve it?