Skip to main content
waffle.svg
Domo Knowledge Base

Period-over-Period in DataFlows

Version 12

 

Intro

You can use DataFlows to create period-over-period (PoP) comparison calculations or other comparative analysis metrics. One drawback from using a DataFlow is that you may lose the ability to have all data fields available in your output and/or dynamic filtering capabilities. Another thing to note is that if you use a DataFlow to create a PoP comparison, it is best practice to keep your ratio calculations in Beast Modes because your ratios are not hardcoded and can be calculated. Beast Mode offers a row-by-row analysis, and the values needed for this calculation are on the same row. 

You can also configure period-over-period charts using the Analyzer. For more information, see Period-over-Period Charts

The following steps represent the basic process and principles of how to accomplish a PoP using DataFlows, but you can perform many types of variations of the calculation based off the examples below.

Steps

  1. Aggregate to the level you want (day granularity is most flexible and will be shown first in the example below).
Note: Don’t forget to sum or average amounts.

Note: Only include the columns needed (depending on what you include, it may not roll up right if too many are used).

Code:

Transform Name: TABLENAME

select

    distinct `account.name` as 'Name',

    closedate as 'Datefield',

    sum(Amount) as 'Amount'

from

      test_randomizer_sf

where `account.name` = 'Lima Worldwide Association'

group by `account.name`, closedate

order by closedate

 

Sample Output:

 

  1. Run your data in chunks and change the date for each chunk.
Note: Instead of subtracting days as you’re a trying to extract data from the past, in SQL, you actually need to add days, as shown in the example below.

Code:

(select `Name`, Amount, date_add(`datefield`, interval 1 day) as 'datefield' from tablename) b -- Yesterday

 

  1. Left-join each chunk back on the base table with an unaltered date, and label it appropriately.
Note: Avoid using a full join or you will lose rows if there are holes in your data.

Code:

select

    a.`Name`,

    a.`Datefield`,

    a.Amount as Today,

    b.Amount as 'Yesterday',

    c.Amount as '7 Days Ago',

    d.Amount as 'Last Month',

    e.Amount as 'Last Year'

from

      tablename a

left join

      (select `Name`, Amount, date_add(`datefield`, interval 1 day) as 'datefield' from tablename) b -- Yesterday

            on a.`Name` = b.`Name` and a.`datefield` = b.`datefield`

left join

      (select `Name`, Amount, date_add(`datefield`, interval 7 day) as 'datefield' from tablename) c -- 7 Days Ago

            on a.`Name` = c.`Name` and a.`datefield` = c.`datefield`

left join

      (select `Name`, Amount, date_add(`datefield`, interval 1 month) as 'datefield' from tablename) d -- Last Month

            on a.`Name` = d.`Name` and a.`datefield` = d.`datefield`

left join

      (select `Name`, Amount, date_add(`datefield`, interval 1 year) as 'datefield' from tablename) e -- Last Year

               on a.`Name` = e.`Name` and a.`datefield` = e.`datefield`        

 

Sample Output:

Note: If the data has holes in it, you may have to backfill data, if possible.

 

Possible Variations

After learning the basics of how to create a PoP using DataFlows, you can do variations on this calculation, such as Month-to-Date (MTD) or Year-to-Date (YTD). The preceding calculations provide you with the architecture you would need to perform these calculations.

The following is another variation of the previous calculation, but it is designed to show a quarterly aggregation:

 

Code (Step1):

Transform Name: TABLENAME

select

    distinct `account.name` as 'Name',    

    Year(closedate) as 'Year',

    quarter(closedate) as 'Quarter',

    concat(Year(closedate),quarter(closedate)) as YQ,

    sum(Amount) as 'Amount'

from

      test_randomizer_sf

where `account.name` = 'Lima Worldwide Association'

group by `account.name`, Year(closedate), quarter(closedate)

order by Year(closedate), quarter(closedate)

 

Sample Output (Step 1):

 

Code (Step 2):

select

    a.`Name`,

    a.`Year`, a.`Quarter`,

    a.Amount as 'Current Quarter',

    b.Amount as 'Last Quarter',

    c.Amount as '2 Quarters Ago',

    d.Amount as '3 Quarters Ago',

    e.Amount as 'Last Year (same qtr)'

from

      tablename a

left join

      (select `Name`, Amount, concat(Case when `Quarter` + 1 = 5 then `Year` + 1 else `Year` end, Case when `Quarter` + 1 = 5 then 1 else `Quarter` + 1 end) as 'YQ'

     from tablename) b -- Last Quarter

      on a.`Name` = b.`Name` and a.`YQ` = b.`YQ`

left join

      (select `Name`, Amount, concat(Case when `Quarter` + 2 >= 5 then `Year` + 1 else `Year` end, Case (`Quarter` + 2) when 5 then 1 when 6 then 2 else `Quarter` + 2 end) as 'YQ'

     from tablename) c -- 2 Quarters Ago

      on a.`Name` = c.`Name` and a.`YQ` = c.`YQ`

left join

      (select `Name`, Amount, concat(Case when `Quarter` + 3 >= 5 then `Year` + 1 else `Year` end, Case (`Quarter` + 3) when 5 then 1 when 6 then 2 when 7 then 3 else `Quarter` + 3 end) as 'YQ'

     from tablename) d -- 2 Quarters Ago

      on a.`Name` = d.`Name` and a.`YQ` = d.`YQ`       

left join

      (select `Name`, Amount, concat(`Year`+1,`Quarter`) as 'YQ'

     from tablename) e -- 1 Year Ago

      on a.`Name` = e.`Name` and a.`YQ` = e.`YQ`

order by a.`Year`, a.`Quarter`

 

Sample Output (Step 2):

 

Video - Percent Change Month-over-Month in ETL

 

 

Two-Part Video - Percent Change Month-over-Month in MySQL

Part 1 of 2

 

 Part 2 of 2