Problem in implementing Period over Period Analysis

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

d5bfabee-c5ce-4f9f-873f-910157608e76.png

I tried to fix it and replaced DOW by day_of_week but no luck

46d1c8cd-4640-4a4f-8bc2-d382df94c192.png

This is what I am trying to run:

c610cdff-caea-49b0-b7d6-d25dea7c35bb.png

Please help me to undertstand what I am missing?

Thanks in advance

Rohit

Solved Solved
0 4 449
1 ACCEPTED SOLUTION

rallaking
Participant I

Hi there, I think “DOW” needs to be replaced with DAYOFWEEK. Give it a shot and let me know if it doesn’t work.

View solution in original post

4 REPLIES 4

rallaking
Participant I

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:

93ab6859-4848-472f-886e-36b4e3f2e096.png

It throws error:

0737ccf9-6056-41aa-ac29-b67adbaaa15a.png

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?

rallaking
Participant I

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:

82c13686-2984-416f-9e4e-447830b44c79.png

Can you please help me to achieve it? 

Top Labels in this Space
Top Solution Authors