Search code examples
looker-studio

Identify elements that do not appear in the period (Google Data Studio)

I have a table that shows the recurrence of purchasing a product, with the columns: product_id, report_date, quantity.

I need to list in a table the products that are more than 50 days unsold. The opposite I managed to do (list those that were sold in the last 50 days) but the opposite logic has not yet been able to implement.

Does anyone have any tips?

An example of the table:

product_id,date,report_date,quantity
329,2019-01-02 08:19:17,2019-01-02 14:34:12,6
243,2019-01-03 09:19:17,2019-01-03 15:34:12,6
238,2019-02-02 08:19:17,2019-03-02 14:34:12,84
170,2019-04-02 08:19:17,2019-04-02 14:34:12,84
238,2019-04-02 08:19:17,2019-04-02 14:34:12,8
238,2019-04-02 08:19:17,2019-04-02 14:34:12,100
238,2019-08-02 08:19:17,2019-08-02 14:34:12,100
238,2019-10-02 08:19:17,2019-10-02 14:34:12,100
170,2020-01-02 08:19:17,2020-01-02 14:34:12,84
170,2020-01-02 08:19:17,2020-01-02 14:34:12,84
Solution

  • There are many steps to do this task. I assume the date column is the one to work with. Your example from table includes duplicated entries. Is it right that at the same time the order is there twice?

    So here are the steps:

    At first add an calculated field date_past to your dataset:

    DATE_DIFF(CURRENT_DATE(),date)

    To the dataset add a filter SO_demo with: include date_past<30 enter image description here

    Then blend the data with it self. Use product_id as Join key. Only the 2nd dataset has the SO_demo filter. Add to the dimension of this dataset the calculated field sold_last_30_days with the formula "yes". enter image description here

    In the table/chart to display add a filter on the field include sold_last_30_days is Null.