time-based-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": false
   },
   "source": [
    "*This is an interactive guide walking you through basics of SAQL, up to most advanced and powerful possibilities of Wave Analytics.*\n",
    "\n",
    "[< Back to homepage](/notebooks)\n",
    "\n",
    "# Time Based Analysis\n",
    "\n",
    "Time-based analysis relies heavily on date functions and ranges, which are documented [here](https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_eql.meta/bi_dev_guide_eql/bi_eql_functions_date.htm). Some common examples are detailed below.\n",
    "\n",
    "## MTD (Month To Date)\n",
    "Calculate the total value of the opportunities expected to close from the beginning of the month through today."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sum_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21078458.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sum_Amount\n",
       "0  21078458.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in [\"current month\"..\"current day\"];\n",
    "q = group q by all;\n",
    "q = foreach q generate sum('Amount') as 'sum_Amount';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The relative date range filters for the beginning of the month through today. As the month or day changes, these keywords will refer to the current month and day.\n",
    "\n",
    "## YTD (Year To Date)\n",
    "\n",
    "Calculate the total value of the opportunities expected to close from the beginning of the year through today."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sum_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>178020414.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    sum_Amount\n",
       "0  178020414.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in [\"current year\"..\"current day\"];\n",
    "q = group q by all;\n",
    "q = foreach q generate sum('Amount') as 'sum_Amount';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## QTD (Quarter To Date)\n",
    "\n",
    "Calculate the total value of the opportunities expected to close from the beginning of the quarter through today."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sum_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21078458.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sum_Amount\n",
       "0  21078458.0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in [\"current quarter\"..\"current day\"];\n",
    "q = group q by all;\n",
    "q = foreach q generate sum('Amount') as 'sum_Amount';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## QoQ (Quarter over Quarter)\n",
    "Calculate the percent change in total value of the opportunities expected to close for the current quarter compared to last quarter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sum_QoQ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-0.035265</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    sum_QoQ\n",
       "0 -0.035265"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "curr = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in [\"current quarter\"..\"current quarter\"];\n",
    "prev = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in [\"1 quarter ago\"..\"1 quarter ago\"];\n",
    "QoQ = cogroup curr by all, prev by all;\n",
    "QoQ = foreach QoQ generate (sum(curr['Amount'])/sum(prev['Amount'])) - 1 as 'sum_QoQ';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** Query Explanation:**\n",
    "\n",
    "After loading in the opportunity dataset, we split it into two streams. One stream, curr, gets data for the current quarter using the date range `[\"current quarter\"..\"current quarter\"]` which filters for data ranging from the start of the current quarter to the end of the current quarter. The other stream, prev, gets data for the previous quarter. We then bring the two streams back together using a co-group. Since we are trying to calculate the overall QoQ number, we can group by alland then perform the QoQ calculation.\n",
    "\n",
    "### Windowing Alternative\n",
    "\n",
    "Let us see how we can do the same QoQ using Windowing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CloseDate_Year~~~CloseDate_Quarter</th>\n",
       "      <th>sum_QoQ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015~~~1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015~~~2</td>\n",
       "      <td>4.060994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015~~~3</td>\n",
       "      <td>-0.128742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015~~~4</td>\n",
       "      <td>1.249819</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016~~~1</td>\n",
       "      <td>0.140070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016~~~2</td>\n",
       "      <td>0.046936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016~~~3</td>\n",
       "      <td>-0.491370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2016~~~4</td>\n",
       "      <td>-0.304678</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CloseDate_Year~~~CloseDate_Quarter   sum_QoQ\n",
       "0                           2015~~~1       NaN\n",
       "1                           2015~~~2  4.060994\n",
       "2                           2015~~~3 -0.128742\n",
       "3                           2015~~~4  1.249819\n",
       "4                           2016~~~1  0.140070\n",
       "5                           2016~~~2  0.046936\n",
       "6                           2016~~~3 -0.491370\n",
       "7                           2016~~~4 -0.304678"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "q = group q by ('CloseDate_Year', 'CloseDate_Quarter');\n",
    "q = foreach q generate 'CloseDate_Year' + \"~~~\" + 'CloseDate_Quarter' as 'CloseDate_Year~~~CloseDate_Quarter',\n",
    "    ((sum('Amount') / sum(sum('Amount')) over([-1 .. -1] partition by all order by ('CloseDate_Year', 'CloseDate_Quarter'))) - 1) as 'sum_QoQ';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After loading the opportunity dataset, We are projecting Year and Quarter as we wanted to see the result in a Time Series and the formula we are using here is (Current Quarter/Previous Quarter) - 1. So if we have a close look at this particular line\n",
    "\n",
    "```pig\n",
    "((sum('Amount') / sum(sum('Amount')) over([-1 .. -1] partition by all order by ('CloseDate_Year', 'CloseDate_Quarter'))) - 1)\n",
    "```\n",
    "\n",
    "The order of execution will be to order the dataset by Year and Quarter ascending and the sum('Amount') which will give the Sum for the current quarter and sum(sum('Amount')) which will give us the sum for the previous quarter because over(-1 .. -1) indicates that we go one previous quarter and subtracting -1 at the end to get QoQ. Since the partitioning is by “all”, windowing will happen across all Year-Quarter."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## QTD over QTD\n",
    "\n",
    "Calculate the percent change in total value of the opportunities expected to close for the current quarter to date compared to last quarter to date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sum_QoQ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-0.035968</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    sum_QoQ\n",
       "0 -0.035968"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "curr = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day')\n",
    "    in [\"current quarter\"..\"current day\"];\n",
    "prev = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day')\n",
    "    in [\"1 quarter ago\"..\"current day - 1 quarter\"];\n",
    "QoQ = group curr by all, prev by all;\n",
    "QoQ = foreach QoQ generate (sum(curr['Amount'])/sum(prev['Amount'])) - 1 as 'sum_QoQ';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Query Explanation:** This query utilizes techniques from the QTD and QoQ calculations. Similar to the QoQ query, we split the dataset into curr and prev streams; however, rather than filtering for the entire quarter, we filter for the quarter to date as we did in the QTD query. The way to get the same number of days from the previous quarter as the number of days elapsed in the current quarter is by using date math. `\"current day - 1 quarter\"` returns the current day relative to the previous quarter.\n",
    "\n",
    "## Current QTD over last year same QTD\n",
    "\n",
    "Calculate the percent change in total value of the opportunities expected to close for the current quarter to date compared to the same period last year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sum_QoQ</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.774138</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    sum_QoQ\n",
       "0  1.774138"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "curr = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in [\"current quarter\"..\"current day\"];\n",
    "prev = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in [\"current quarter - 1 year\"..\"current day - 1 year\"];\n",
    "QoQ = group curr by all, prev by all;\n",
    "QoQ = foreach QoQ generate (sum(curr['Amount'])/sum(prev['Amount'])) - 1 as 'sum_QoQ';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Query Explanation:** This query is similar to the QTD over QTD calculation. The only difference is that we refer to the previous year using relative date math `[\"current quarter - 1 year\"..\"current day - 1 year\"]`.\n",
    "\n",
    "## Most recent available month\n",
    "\n",
    "There are various reasons that your data could be out of date. For example, your bookings from an external data source may only get updated once a month on the 5th. On March 1 - 4, you still want to report off of January data. In these cases, standard relative dates may not be appropriate. Filtering for “previous month” on February 28th will give us our previous month’s bookings data; however, on March 1st, the “previous month” filter will look for data for February’s bookings data, which does not get entered into the system until four more days. The work around for this is to use the following steps:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CloseDate_Month</th>\n",
       "      <th>CloseDate_Year</th>\n",
       "      <th>sum_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>4571092</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CloseDate_Month CloseDate_Year  sum_Amount\n",
       "0              12           2016     4571092"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "q = group q by ('CloseDate_Year', 'CloseDate_Month');\n",
    "q = foreach q generate 'CloseDate_Year', 'CloseDate_Month', sum('Amount') as 'sum_Amount';\n",
    "q = order q by ('CloseDate_Year' desc, 'CloseDate_Month' desc);\n",
    "q = limit q 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Query Explanation:** The objective of this query is to order the results by date in descending order and then select the first record to get the most recent date. This is achieved by ordering by the year and then month, both in descending order. The sequence in which you order is important here; for example, ordering by month and then year would give you different results. Since we are trying to find the most recent month available, we apply a group at the\n",
    "year-month level. If we wanted the most recent available day, the grouping would be `('CloseDate_year', 'CloseDate_month', 'CloseDate_day')`. For most recent available quarter, `('CloseDate_year', 'CloseDate_quarter')`. If we were to try using standard relative dates like in the query below, the result would be $0 because there is no data for the current month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sum_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>177416756.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    sum_Amount\n",
       "0  177416756.0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in [\"current quarter\"..\"current quarter\"];\n",
    "q = group q by all;\n",
    "q = foreach q generate sum('Amount') as 'sum_Amount';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Please note that $0 is not the incorrect result for the query. Rather, the query itself does not address the question that is proposed.\n",
    "\n",
    "## MoM on most recent available month\n",
    "\n",
    "Calculate the percent change in total value of the opportunities expected to close for the most\n",
    "recent available month compared to the month before it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sum_MoM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-0.873369</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    sum_MoM\n",
       "0 -0.873369"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "q = group q by ('CloseDate_Year', 'CloseDate_Month');\n",
    "q = foreach q generate 'CloseDate_Year', 'CloseDate_Month', sum('Amount') as 'sum_Amount';\n",
    "q = order q by ('CloseDate_Year' desc, 'CloseDate_Month' desc);\n",
    "curr = limit q 1;\n",
    "prev = offset q 1;\n",
    "prev = limit prev 1;\n",
    "MoM = group curr by all, prev by all;\n",
    "MoM = foreach MoM generate (sum(curr['sum_Amount'])/sum(prev['sum_Amount'])) - 1 as 'sum_MoM';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Query Explanation:** This query builds upon the query to get the most recent available month. After ordering by year and month, we create two separate streams. currrepresents the most recent available month and prevrepresents the preceding month. curris the first record in the projection which is why we apply the limit of 1. We can get prev, which is the second record in the projection, by offsetting the projection by 1 and then getting the first record of this projection.\n",
    "\n",
    "## Windowing Alternative\n",
    "\n",
    "Let us see how we can do the same MoM on recent available month using Windowing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CloseDate_Month</th>\n",
       "      <th>CloseDate_Year</th>\n",
       "      <th>sum_MostRecentAvailableMoM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>03</td>\n",
       "      <td>2015</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>04</td>\n",
       "      <td>2015</td>\n",
       "      <td>35.782624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>05</td>\n",
       "      <td>2015</td>\n",
       "      <td>12.635092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>06</td>\n",
       "      <td>2015</td>\n",
       "      <td>42.133855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>07</td>\n",
       "      <td>2015</td>\n",
       "      <td>-34.095057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>08</td>\n",
       "      <td>2015</td>\n",
       "      <td>-27.230394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>09</td>\n",
       "      <td>2015</td>\n",
       "      <td>85.540045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10</td>\n",
       "      <td>2015</td>\n",
       "      <td>42.240955</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>11</td>\n",
       "      <td>2015</td>\n",
       "      <td>45.666697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>12</td>\n",
       "      <td>2015</td>\n",
       "      <td>-21.121832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>01</td>\n",
       "      <td>2016</td>\n",
       "      <td>27.483258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>02</td>\n",
       "      <td>2016</td>\n",
       "      <td>10.732593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>03</td>\n",
       "      <td>2016</td>\n",
       "      <td>-36.870449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>04</td>\n",
       "      <td>2016</td>\n",
       "      <td>6.613336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>05</td>\n",
       "      <td>2016</td>\n",
       "      <td>48.745237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>06</td>\n",
       "      <td>2016</td>\n",
       "      <td>-2.194126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>07</td>\n",
       "      <td>2016</td>\n",
       "      <td>-40.108662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>08</td>\n",
       "      <td>2016</td>\n",
       "      <td>-20.488856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>09</td>\n",
       "      <td>2016</td>\n",
       "      <td>-36.335457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10</td>\n",
       "      <td>2016</td>\n",
       "      <td>11.221143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>11</td>\n",
       "      <td>2016</td>\n",
       "      <td>63.510734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>-87.336894</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CloseDate_Month CloseDate_Year  sum_MostRecentAvailableMoM\n",
       "0               03           2015                         NaN\n",
       "1               04           2015                   35.782624\n",
       "2               05           2015                   12.635092\n",
       "3               06           2015                   42.133855\n",
       "4               07           2015                  -34.095057\n",
       "5               08           2015                  -27.230394\n",
       "6               09           2015                   85.540045\n",
       "7               10           2015                   42.240955\n",
       "8               11           2015                   45.666697\n",
       "9               12           2015                  -21.121832\n",
       "10              01           2016                   27.483258\n",
       "11              02           2016                   10.732593\n",
       "12              03           2016                  -36.870449\n",
       "13              04           2016                    6.613336\n",
       "14              05           2016                   48.745237\n",
       "15              06           2016                   -2.194126\n",
       "16              07           2016                  -40.108662\n",
       "17              08           2016                  -20.488856\n",
       "18              09           2016                  -36.335457\n",
       "19              10           2016                   11.221143\n",
       "20              11           2016                   63.510734\n",
       "21              12           2016                  -87.336894"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "q = group q by ('CloseDate_Year', 'CloseDate_Month');\n",
    "q = foreach q generate 'CloseDate_Year', 'CloseDate_Month',\n",
    "    ((sum('Amount') / sum(sum('Amount')) over([1 .. 1] partition by all order by ('CloseDate_Year' desc, 'CloseDate_Month' desc))) - 1) * 100 as 'sum_MostRecentAvailableMoM';\n",
    "q = order q by ('CloseDate_Year', 'CloseDate_Month');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Query Explanation:**\n",
    "```pig\n",
    "((sum('Amount') / sum(sum('Amount')) over([1 .. 1] partition by all order by ('CloseDate_Year' desc, 'CloseDate_Month' desc))) - 1)\n",
    "```\n",
    "\n",
    "The order of execution will be to order the dataset by Year and Month ascending and the `sum('Amount')` which will give the Sum for the current month and `sum(sum('Amount'))` which will give us the sum for the previous month because `over([1 .. 1])` indicates that we go one month previous and subtracting -1 at the end to get MoM. Since the partitioning is by “all”, windowing will happen across all Year-Month. At the end since we are limiting the result set\n",
    "by 1, we will only get the Most recent available Month in the dataset.\n",
    "\n",
    "Note that even though the windowing function contains the keywords “order by”, the resulting stream is not guaranteed to be ordered (the “order by” here is for the calculation). This is why there is a separate “order by” statement following the windowing function.\n",
    "\n",
    "## Timeline Comparisons\n",
    "\n",
    "Another type of time-based analysis is comparing how we are trending in the current quarter versus the same time last year. This type of analysis is useful for seeing if we are on-track, lagging, or ahead by using a previous time period as a benchmark."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CreatedDate_Year~~~CreatedDate_Week</th>\n",
       "      <th>Date</th>\n",
       "      <th>sum_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016~~~14</td>\n",
       "      <td>This Quarter</td>\n",
       "      <td>19153027.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016~~~15</td>\n",
       "      <td>This Quarter</td>\n",
       "      <td>19110473.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016~~~16</td>\n",
       "      <td>This Quarter</td>\n",
       "      <td>10769424.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016~~~17</td>\n",
       "      <td>This Quarter</td>\n",
       "      <td>10283451.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016~~~18</td>\n",
       "      <td>This Quarter</td>\n",
       "      <td>14849622.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016~~~19</td>\n",
       "      <td>This Quarter</td>\n",
       "      <td>10523830.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016~~~20</td>\n",
       "      <td>This Quarter</td>\n",
       "      <td>11574725.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2016~~~21</td>\n",
       "      <td>This Quarter</td>\n",
       "      <td>4580310.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2016~~~14</td>\n",
       "      <td>1 Year Ago</td>\n",
       "      <td>5374352.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016~~~15</td>\n",
       "      <td>1 Year Ago</td>\n",
       "      <td>9086455.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2016~~~16</td>\n",
       "      <td>1 Year Ago</td>\n",
       "      <td>6177107.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2016~~~17</td>\n",
       "      <td>1 Year Ago</td>\n",
       "      <td>7773411.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2016~~~18</td>\n",
       "      <td>1 Year Ago</td>\n",
       "      <td>10422899.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2016~~~19</td>\n",
       "      <td>1 Year Ago</td>\n",
       "      <td>1439656.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2016~~~20</td>\n",
       "      <td>1 Year Ago</td>\n",
       "      <td>9017189.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2016~~~21</td>\n",
       "      <td>1 Year Ago</td>\n",
       "      <td>12076306.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CreatedDate_Year~~~CreatedDate_Week          Date  sum_Amount\n",
       "0                            2016~~~14  This Quarter  19153027.0\n",
       "1                            2016~~~15  This Quarter  19110473.0\n",
       "2                            2016~~~16  This Quarter  10769424.0\n",
       "3                            2016~~~17  This Quarter  10283451.0\n",
       "4                            2016~~~18  This Quarter  14849622.0\n",
       "5                            2016~~~19  This Quarter  10523830.0\n",
       "6                            2016~~~20  This Quarter  11574725.0\n",
       "7                            2016~~~21  This Quarter   4580310.0\n",
       "8                            2016~~~14    1 Year Ago   5374352.0\n",
       "9                            2016~~~15    1 Year Ago   9086455.0\n",
       "10                           2016~~~16    1 Year Ago   6177107.0\n",
       "11                           2016~~~17    1 Year Ago   7773411.0\n",
       "12                           2016~~~18    1 Year Ago  10422899.0\n",
       "13                           2016~~~19    1 Year Ago   1439656.0\n",
       "14                           2016~~~20    1 Year Ago   9017189.0\n",
       "15                           2016~~~21    1 Year Ago  12076306.0"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "ThisQ = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in [\"current quarter\"..\"current quarter\"];\n",
    "LastQ = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in [\"current quarter - 1 year\"..\"current quarter - 1 year\"];\n",
    "LastQGrp = cogroup ThisQ by (CreatedDate_Week) left, LastQ by (CreatedDate_Week);\n",
    "LastQGrp = foreach LastQGrp generate\n",
    "    first(ThisQ['CreatedDate_Year']) + \"~~~\" + ThisQ['CreatedDate_Week'] as 'CreatedDate_Year~~~CreatedDate_Week',\n",
    "    \"1 Year Ago\" as 'Date', sum(LastQ['Amount']) as 'sum_Amount';\n",
    "ThisQ = group ThisQ by (CreatedDate_Year, CreatedDate_Week);\n",
    "ThisQ = foreach ThisQ generate 'CreatedDate_Year' + \"~~~\" + 'CreatedDate_Week' as 'CreatedDate_Year~~~CreatedDate_Week',\n",
    "    \"This Quarter\" as 'Date',\n",
    "    sum('Amount') as 'sum_Amount';\n",
    "c = union ThisQ, LastQGrp;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dashboard JSON version:\n",
    "\n",
    "```json\n",
    "\"pigql\": \"q = load \\\"opportunities\\\"; ThisQ = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in [\\\"current quarter\\\"..\\\"current quarter\\\"]; LastQ = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in [\\\"current quarter - 1 year\\\"..\\\"current quarter - 1 year\\\"]; LastQGrp = cogroup ThisQ by (CreatedDate_Week) left, LastQ by (CreatedDate_Week); LastQGrp = foreach LastQGrp generate first(ThisQ['CreatedDate_Year']) + \\\"~~~\\\" + ThisQ['CreatedDate_Week'] as 'CreatedDate_Year~~~CreatedDate_Week', \\\"1 Year Ago\\\" as 'Date', sum(LastQ['Amount']) as 'sum_Amount'; ThisQ = group ThisQ by (CreatedDate_Year, CreatedDate_Week); ThisQ = foreach ThisQ generate 'CreatedDate_Year' + \\\"~~~\\\" + 'CreatedDate_Week' as 'CreatedDate_Year~~~CreatedDate_Week', \\\"This Quarter\\\" as 'Date', sum('Amount') as 'sum_Amount'; c = union ThisQ, LastQGrp;\",\n",
    "```\n",
    "\n",
    "In this example we have two SAQL streams. One for this quarter's data which should hopefully be straightforward and one for the same quarter last year. To explain what we did with the 1 Year Ago stream, it's important to note that in order to use the time chart the final SAQL stream must be grouped by a valid date dimension and include the year and if we need to include the year we need to make sure the two separate data streams have the same year.\n",
    "\n",
    "To manipulate the year in last year data stream, we cogrouped this quarter with 1 year ago using the CreatedDate_Week dimension. In the projection, we took the year (we needed to use the first() function to get this since it wasn't used in my grouping) and month from the \"This Quarter\" stream and the sum of Amount from the \"1 Year Ago\" stream. This projection gives the count of created cases by week for the quarter one year ago but instead of my date field containing 2014 it contains 2015 so the data will show up on the time chart.\n",
    "\n",
    "Finally we union the two streams together which are both grouped by year and month."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bonus SWAG for Training"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "User \"kim@waverocks.de\" running query on https://na30.salesforce.com\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CloseDate_Year~~~CloseDate_Month</th>\n",
       "      <th>sum_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015~~~03</td>\n",
       "      <td>10734492.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015~~~04</td>\n",
       "      <td>24030965.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015~~~05</td>\n",
       "      <td>46686821.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015~~~06</td>\n",
       "      <td>71158421.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015~~~07</td>\n",
       "      <td>112158317.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2015~~~08</td>\n",
       "      <td>138020736.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2015~~~09</td>\n",
       "      <td>153853981.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2015~~~10</td>\n",
       "      <td>180748469.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2015~~~11</td>\n",
       "      <td>253590823.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2015~~~12</td>\n",
       "      <td>302984416.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2016~~~01</td>\n",
       "      <td>357544636.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2016~~~02</td>\n",
       "      <td>421131313.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2016~~~03</td>\n",
       "      <td>476060234.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2016~~~04</td>\n",
       "      <td>525174408.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2016~~~05</td>\n",
       "      <td>571505143.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2016~~~06</td>\n",
       "      <td>643032459.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2016~~~07</td>\n",
       "      <td>692269828.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2016~~~08</td>\n",
       "      <td>723394194.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2016~~~09</td>\n",
       "      <td>749525042.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2016~~~10</td>\n",
       "      <td>779834738.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2016~~~11</td>\n",
       "      <td>793061965.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2016~~~12</td>\n",
       "      <td>821856397.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2017~~~01</td>\n",
       "      <td>822751687.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CloseDate_Year~~~CloseDate_Month   sum_Amount\n",
       "0                         2015~~~03   10734492.0\n",
       "1                         2015~~~04   24030965.0\n",
       "2                         2015~~~05   46686821.0\n",
       "3                         2015~~~06   71158421.0\n",
       "4                         2015~~~07  112158317.0\n",
       "5                         2015~~~08  138020736.0\n",
       "6                         2015~~~09  153853981.0\n",
       "7                         2015~~~10  180748469.0\n",
       "8                         2015~~~11  253590823.0\n",
       "9                         2015~~~12  302984416.0\n",
       "10                        2016~~~01  357544636.0\n",
       "11                        2016~~~02  421131313.0\n",
       "12                        2016~~~03  476060234.0\n",
       "13                        2016~~~04  525174408.0\n",
       "14                        2016~~~05  571505143.0\n",
       "15                        2016~~~06  643032459.0\n",
       "16                        2016~~~07  692269828.0\n",
       "17                        2016~~~08  723394194.0\n",
       "18                        2016~~~09  749525042.0\n",
       "19                        2016~~~10  779834738.0\n",
       "20                        2016~~~11  793061965.0\n",
       "21                        2016~~~12  821856397.0\n",
       "22                        2017~~~01  822751687.0"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%saql\n",
    "q = load \"opportunities\";\n",
    "q = group q by (CloseDate_Year, CloseDate_Month);\n",
    "q = foreach q generate 'CloseDate_Year' + \"~~~\" + 'CloseDate_Month' as 'CloseDate_Year~~~CloseDate_Month',\n",
    "    sum(sum('Amount')) over([.. 0] partition by all order by (CloseDate_Year, CloseDate_Month)) as 'sum_Amount';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dashboard JSON:\n",
    "```json\n",
    "\"query\": {\n",
    "    \"pigql\": \"q = load \\\"opportunities\\\"; q = group q by (CloseDate_Year, CloseDate_Month); q = foreach q generate 'CloseDate_Year' + \\\"~~~\\\" + 'CloseDate_Month' as 'CloseDate_Year~~~CloseDate_Month', sum(sum('Amount')) over([.. 0] partition by all order by (CloseDate_Year, CloseDate_Month)) as 'sum_Amount';\",\n",
    "    \"measures\": [\n",
    "        [\n",
    "            \"sum\",\n",
    "            \"Amount\"\n",
    "        ]\n",
    "    ],\n",
    "    \"groups\": [\n",
    "        [\n",
    "            \"CloseDate_Year\",\n",
    "            \"CloseDate_Month\"\n",
    "        ]\n",
    "    ]\n",
    "},\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}