BigQuery Period to Date

ptd.view.lkml

include: "mybaseview.view.lkml"

#add fields onto my existing base view
view: +mybaseview {
  parameter: timeframe {
    view_label: "Period over Period"
    type: unquoted
    allowed_value: {
      label: "Week to Date"
      value: "Week"
    }
    allowed_value: {
      label: "Month to Date"
      value: "Month"
    }
    allowed_value: {
      label: "Quarter to Date"
      value: "Quarter"
    }
    allowed_value: {
      label: "Year to Date"
      value: "Year"
    }
    default_value: "Quarter"
  }

  # To get start date we need to get either first day of the year, month or quarter
  dimension: first_date_in_period {
    view_label: "Period over Period"
    type: date
    hidden: no
    sql: DATE_TRUNC(CURRENT_DATE(), {% parameter timeframe %});;
  }

  #Now get the total number of days in the period
  dimension: days_in_period {
    view_label: "Period over Period"
    type: number
    hidden: no
    sql: DATE_DIFF(CURRENT_DATE(),${first_date_in_period}, DAY) ;;
  }

  #Now get the first date in the prior period
  dimension: first_date_in_prior_period {
    view_label: "Period over Period"
    type: date
    hidden: no
    sql: DATE_TRUNC(DATE_ADD(CURRENT_DATE(), INTERVAL -1 {% parameter timeframe %}),{% parameter timeframe %});;
  }

  #Now get the last date in the prior period
  dimension: last_date_in_prior_period {
    view_label: "Period over Period"
    type: date
    hidden: no
    sql: DATE_ADD(${first_date_in_prior_period}, INTERVAL ${days_in_period} DAY) ;;
  }

  # Now figure out which period each date belongs in (update with your own date dimension that you want to leverage)
  dimension: period_selected {
    view_label: "Period over Period"
    type: string
    sql:
        CASE
          WHEN ${my_date} >=  ${first_date_in_period}
          THEN 'This {% parameter timeframe %} to Date'
          WHEN ${my_date} >= ${first_date_in_prior_period}
          AND ${my_date} <= ${last_date_in_prior_period}
          THEN 'Prior {% parameter timeframe %} to Date'
          ELSE NULL
          END ;;
  }
}

PTD_Analysis.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Date_Analysis.ipynb",
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "Fhl1T0s2ojM3"
      },
      "source": [
        "# Import libraries\n",
        "from google.cloud import bigquery\n",
        "from google.colab import auth\n",
        "\n",
        "# A link to authenticate through Oauth will pop up \n",
        "auth.authenticate_user()"
      ],
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2As-nRAGwFz3"
      },
      "source": [
        "# Construct a BigQuery client object (update with your project)\n",
        "client = bigquery.Client(project=\"my-project\")"
      ],
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jOyzyQLmwGLf"
      },
      "source": [
        "# Create a user specified timeframe \n",
        "timeframe = \"quarter\" #@param [\"week\", \"month\", \"quarter\",\"year\"]\n"
      ],
      "execution_count": 31,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hvZO79-Jojne",
        "outputId": "5ee58923-9316-4cbb-d78d-d80526ee7ed0"
      },
      "source": [
        "# Get the start of the current period\n",
        "query = \"SELECT DATE_TRUNC(CURRENT_DATE(),{timeframe}) as current_period_start\".format(timeframe=timeframe)\n",
        "\n",
        "query_job = client.query(query)\n",
        "\n",
        "for row in query_job:\n",
        "    print(\"Current Period Start: {}\".format(row.current_period_start))"
      ],
      "execution_count": 36,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Current Period Start: 2021-04-01\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wGjD-MJdzoh2",
        "outputId": "3d339407-d093-4165-ae29-f673623a4409"
      },
      "source": [
        "# Get the first day of the prior period\n",
        "query = \"SELECT DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 {timeframe}), {timeframe}) as prior_period_start\".format(timeframe=timeframe)\n",
        "\n",
        "query_job = client.query(query)\n",
        "\n",
        "for row in query_job:\n",
        "    print(\"Prior Period Start: {}\".format(row.prior_period_start))\n"
      ],
      "execution_count": 37,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Prior Period Start: 2021-01-01\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "vVym6XhnpFAg",
        "outputId": "fda8dec2-1fe2-4e35-be91-f8b54d6191db"
      },
      "source": [
        "# Get the last day in the prior period\n",
        "query = \"\"\"SELECT DATE_DIFF(CURRENT_DATE(), DATE_TRUNC(CURRENT_DATE(),{timeframe}), DAY) as days_in_period,\n",
        "                  DATE_ADD(DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 {timeframe}), {timeframe}), INTERVAL DATE_DIFF(CURRENT_DATE(), DATE_TRUNC(CURRENT_DATE(),{timeframe}), DAY) DAY) as prior_period_end\"\"\".format(timeframe=timeframe)\n",
        "\n",
        "query_job = client.query(query)\n",
        "\n",
        "for row in query_job:\n",
        "    print(\"Days in period: {} \\nPrior Period End: {}\".format(row.days_in_period, row.prior_period_end))"
      ],
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Days in period: 12 \n",
            "Prior Period End: 2021-01-13\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tAVOJjNFsgJE",
        "outputId": "dab58899-de66-461f-f14d-7fea4afe3e4c"
      },
      "source": [
        "# Create a user specified timeframe \n",
        "timeframe = \"year\" #@param [\"week\", \"month\", \"quarter\",\"year\"]\n",
        "\n",
        "\n",
        "### Put it all together in one query \n",
        "query = \"\"\"SELECT \n",
        "--my date is after the current period start\n",
        "CASE WHEN {my_date_column} >=  DATE_TRUNC(CURRENT_DATE(),{timeframe})\n",
        "THEN 'This {timeframe} to Date'\n",
        "--my date is between the prior period start and end\n",
        "WHEN {my_date_column} >= DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 {timeframe}), {timeframe})\n",
        "AND {my_date_column} <= DATE_ADD(DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 {timeframe}), {timeframe}), INTERVAL DATE_DIFF(CURRENT_DATE(), DATE_TRUNC(CURRENT_DATE(),{timeframe}), DAY) DAY)\n",
        "THEN 'Prior {timeframe} to Date'\n",
        "ELSE NULL\n",
        "END as time_period, \n",
        "round(sum({my_summed_column}),2) as total\n",
        "from {my_table}\n",
        "group by 1\"\"\".format(\n",
        "    timeframe=timeframe,\n",
        "    #update these values for your own!\n",
        "    my_table=\"looker-private-demo.ecomm.order_items\", \n",
        "    my_summed_column=\"sale_price\", \n",
        "    my_date_column=\"date(created_at)\")\n",
        "\n",
        "query_job = client.query(query)\n",
        "\n",
        "for row in query_job:\n",
        "    print(\"{}: {}\".format(row.time_period, row.total))"
      ],
      "execution_count": 70,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "None: 6939761.8\n",
            "Prior year to Date: 958911.61\n",
            "This year to Date: 1444845.79\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uDuqqhYIs0ew"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}