A more flexible way to do Period over Period comparisons

GrantSmith
GrantSmith Coach
edited March 2023 in Scheduled Reports

Greetings! I see variations of this topic asked several times so I thought I'd write up all of the information in a singular spot for easier reference. Below I'll outline how I utilize my own date dimension table to have customized period over period charts, compare the current partial month to the same amount of time in the previous period (no more seeing your period over period percentage tank because the month isn't complete yet). I will also go over how to configure and set this up yourselves. Let's get started.

**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**

Comments

  • Dataset Format Overview

    The resulting dataset will have three different columns:

    • Report Date - This is the date you will use in your charts / graphs
    • Comparison Date - This is the date you will join your dataset to
    • Period Type - This defines the type of the period / offset


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Base Date Dimension

    To start we need a list of dates. Domo provides one in their date dimension connector. You can get it installed here (replace [CUSTOMER] with your instance name):

    https://[CUSTOMER].domo.com/appstore/connectors/com.domo.connector.domodimension?origin=dc

    The file you'll want to import from the list is calendar.csv

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Configuring The Periods

    The next step is to configure the different periods. In this instance it was easiest for me to utilize a MySQL dataflow but the same logical process could be utilized in a MagicETL dataflow as well. I've outlined code to calculate the following periods however it can be expanded using the same logic.

    • Current
    • Last Week
    • Last Month
    • 28 Days ago
    • Last Year (DOW Aligned) - Day of Week Aligned to compare Monday this year to Monday last year for example
    • Last Year
    • 2 Years Ago (DOW Aligned)
    • 2 years ago
    • Last Quarter


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MySQL

    Use the Date Dimension dataset you pulled in under the Base Date Dimension step above as the input to a new MySQL dataflow. Note: I named by input dataset calendar_dates

    Add a Table transform and call it dates with the following code:

    select d.`dt`
    from `calendar_dates` d
    

    Put this code into the output datasets:

    select d.`dt` as "Report Date", date_sub(`dt`, interval '0' year) as "Comparison Date", 'Current' as 'Period Type'
    from `dates` d
    
    UNION
    
    select d.`dt` as "Report Date", date_sub(`dt`, interval '7' day) as "Comparison Date", 'Last Week' as 'Period Type'
    from `dates` d
    
    UNION
    
    select d.`dt` as "Report Date", date_sub(`dt`, interval '1' month) as "Comparison Date", 'Last Month' as 'Period Type'
    from `dates` d
    
    UNION
    
    select d.`dt` as "Report Date", date_sub(`dt`, interval '28' day) as "Comparison Date", '28 Days Ago' as 'Period Type'
    from `dates` d
    
    UNION
    
    select d.`dt` as "Report Date", date_sub(`dt`, interval '364' day) as "Comparison Date", 'Last Year (DOW Aligned)' as 'Period Type'
    from `dates` d
    
    UNION
    
    select d.`dt` as "Report Date", date_sub(`dt`, interval '1' year) as "Comparison Date", 'Last Year' as 'Period Type'
    from `dates` d
    
    UNION
    
    select d.`dt` as "Report Date", date_sub(`dt`, interval '2' year) as "Comparison Date", '2 Years Ago' as 'Period Type'
    from `dates` d
    
    UNION
    
    select d.`dt` as "Report Date", date_sub(`dt`, interval '728' day) as "Comparison Date", '2 Years Ago (DOW Aligned)' as 'Period Type'
    from `dates` d
    
    UNION
    
    select d.`dt` as "Report Date", date_sub(`dt`, interval '3' month) as "Comparison Date", 'Last Quarter' as 'Period Type'
    from `dates` d
    
    


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Magic ETL

    This code will work for Magic 1 or Magic 2. Just cope the code in the code block below and then use Command+V (Mac) or Control+V (Windows) to paste the code into your Magic ETL palette. You will need to define the input dataset and the output dataset.

    {"contentType":"domo/dataflow-actions","data":[{"name":"[DIM] Calendar Dates","id":"3cc1f500-f11b-495e-b563-e2b8422626a1","type":"LoadFromVault","gui":{"x":36,"y":180,"color":3238043},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null},{"name":"Current Date","id":"551fbad1-b100-44da-8ade-4ef2243b1e75","type":"DateCalculator","gui":{"x":252,"y":180},"dependsOn":["1662a58a-fc68-4d7b-a981-11e4a546db6a"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"0","type":"LONG"},"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"Current","id":"1662a58a-fc68-4d7b-a981-11e4a546db6a","type":"Constant","gui":{"x":144,"y":180},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Current"}]},{"name":"Last Week Date","id":"a7b6768c-2b7d-4c88-ab83-c3bbe8fff0c3","type":"DateCalculator","gui":{"x":252,"y":276},"dependsOn":["0b91dd40-bdf0-4068-9ce0-258ec568138c"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"7","type":"LONG"},"constantC":{"type":"STRING","value":"-DAYS"}}]},{"name":"Last Week","id":"0b91dd40-bdf0-4068-9ce0-258ec568138c","type":"Constant","gui":{"x":144,"y":276},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Last Week"}]},{"name":"Last Month Date","id":"e0a38130-cf41-4056-858a-7825162ebf63","type":"DateCalculator","gui":{"x":252,"y":372},"dependsOn":["aafe8e71-83d5-483b-8bb7-626bdf0e6eb8"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"0","type":"LONG"},"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"Last Month","id":"aafe8e71-83d5-483b-8bb7-626bdf0e6eb8","type":"Constant","gui":{"x":144,"y":372},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Last Month"}]},{"name":"28 Days Ago Date","id":"4d7d621c-4491-48bd-99d4-e1e7f4a748e7","type":"DateCalculator","gui":{"x":252,"y":468},"dependsOn":["0037e619-4cae-4bdc-82f8-7b6ed8fb7710"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"0","type":"LONG"},"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"28 Days Ago","id":"0037e619-4cae-4bdc-82f8-7b6ed8fb7710","type":"Constant","gui":{"x":144,"y":468},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"28 Days Ago"}]},{"name":"Last Year Date","id":"e275d916-e4f0-454e-85e8-f1469c8ed0df","type":"DateCalculator","gui":{"x":252,"y":564},"dependsOn":["b603d5e9-2d37-4254-a973-280c32bfe867"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"0","type":"LONG"},"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"Last Year","id":"b603d5e9-2d37-4254-a973-280c32bfe867","type":"Constant","gui":{"x":144,"y":564},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Last Year"}]},{"name":"Last Year DOW Date","id":"584b70ed-7b8c-43b5-a417-bfa60ea324b0","type":"DateCalculator","gui":{"x":252,"y":660},"dependsOn":["e8ab3aef-24de-4110-be60-67f47d549518"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"364","type":"LONG"},"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"Last Year DOW","id":"e8ab3aef-24de-4110-be60-67f47d549518","type":"Constant","gui":{"x":144,"y":660},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Last Year DOW"}]},{"name":"2 Years Ago Date","id":"8c74ae9d-7b61-49d1-9b8b-a5e091166a8a","type":"DateCalculator","gui":{"x":252,"y":756},"dependsOn":["296ce080-f4ab-4478-8bef-ec943e8457cd"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"24","type":"LONG"},"constantC":{"type":"STRING","value":"-MONTHS"}}]},{"name":"2 Years Ago","id":"296ce080-f4ab-4478-8bef-ec943e8457cd","type":"Constant","gui":{"x":144,"y":756},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"2 Years Ago"}]},{"name":"2 Years Ago DOW Date","id":"62391242-890e-4ffb-a841-a01c9e981f5a","type":"DateCalculator","gui":{"x":252,"y":852},"dependsOn":["49f8ca7e-922f-48c0-8033-010a6c76cf5b"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"728","type":"LONG"},"constantC":{"type":"STRING","value":"-DAYS"}}]},{"name":"2 Years Ago DOW","id":"49f8ca7e-922f-48c0-8033-010a6c76cf5b","type":"Constant","gui":{"x":144,"y":852},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"2 Years Ago (DOW Aligned)"}]},{"name":"Last Quarter Date","id":"4a3ad517-7717-4ec3-89d6-96d8da12d9c8","type":"DateCalculator","gui":{"x":252,"y":948},"dependsOn":["3640e331-0c91-434f-b1a7-37b81158dfa9"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","constantB":{"value":"3","type":"LONG"},"constantC":{"type":"STRING","value":"-MONTHS"}}]},{"name":"Lat Quarter","id":"3640e331-0c91-434f-b1a7-37b81158dfa9","type":"Constant","gui":{"x":144,"y":948},"dependsOn":["3cc1f500-f11b-495e-b563-e2b8422626a1"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","value":"Last Quarter"}]},{"name":"Append Rows","id":"46436b73-0ad7-43f8-b2db-cf5d06c5a545","type":"UnionAll","gui":{"x":540,"y":564},"dependsOn":["551fbad1-b100-44da-8ade-4ef2243b1e75","a7b6768c-2b7d-4c88-ab83-c3bbe8fff0c3","e0a38130-cf41-4056-858a-7825162ebf63","4d7d621c-4491-48bd-99d4-e1e7f4a748e7","e275d916-e4f0-454e-85e8-f1469c8ed0df","584b70ed-7b8c-43b5-a417-bfa60ea324b0","8c74ae9d-7b61-49d1-9b8b-a5e091166a8a","62391242-890e-4ffb-a841-a01c9e981f5a","4a3ad517-7717-4ec3-89d6-96d8da12d9c8"],"removeByDefault":false,"notes":[],"unionType":"INCLUDE_ALL","strict":false},{"name":"Select Columns","id":"4ff2d5d9-25e0-4d7a-b745-6ce907fced89","type":"SelectValues","gui":{"x":647,"y":564},"dependsOn":["46436b73-0ad7-43f8-b2db-cf5d06c5a545"],"removeByDefault":false,"notes":[],"fields":[{"name":"dt","rename":"Report Date"},{"name":"Period Type"},{"name":"Comparison Date"}]},{"name":"Dates with Offsets","id":"ef556540-dacb-465c-ba0c-b2b4f614b902","type":"PublishToVault","gui":{"x":756,"y":564},"dependsOn":["4ff2d5d9-25e0-4d7a-b745-6ce907fced89"],"removeByDefault":false,"notes":[],"dataSource":{"name":"Dates with Offsets"},"partitionIdColumns":[]}]}
    


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Joining Datasets Together

    Once you have your dataset with the different user defined periods created from above you're ready to join it to your original dataset. You can use any of the options at your disposal (MySQL Dataflow, Magic ETL dataflow, Redshit dataflow, Fusions) however my recommendation are the new Dataset Views as they're faster and easier to utilize.

    The important part here is to make sure you're joining your dataset on the date you wish to graph to the comparison date field, not the report date.

    Note

    By joining your two datasets together it will duplicate each record in your dataset based on the amount of defined periods. This is why I like to use Views as they don't count against your row limit. If you're concerned about the size of the dataset you can filter down or remove periods you won't utilize.

    Starting with your Period dataset create a view then left join your dataset you're wanting to evaluate period over period with:


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Graphing Your Period over Period

    The traditional period over period type graphs won't work with this method however there is a really good alternative in the Line+Bar graphs. This will allow you to plot the different period types along with the percentage difference. You just need to do a few best modes depending on what you're wanting to graph.


    Utilizing Beast Modes to Calculate Period Types

    In order to chart the different period types we'll need to extract them from our dataset utilizing some beast modes. They will have the following format:

    Current date:

    CASE WHEN `Period Type` = 'Current' THEN `Value to plot` END
    

    Last Year:

    CASE WHEN `Period Type` = 'Last Year' THEN `Value to plot` END
    

    etc.

    If you prefer you can utilize an aggregate function in your beast mode which surrounds the CASE statement or, as it's currently implemented, rely on selecting your aggregation within the chart columns.

    Period over Period Percent

    To calculate the percentage you'll need to utilize another beast mode and the SUM aggregation. You'll then need to format this as a percentage in the column options. The following example is for Last Year.

    (SUM(CASE WHEN `Period Type` = 'Current' THEN `Value to plot` END)
    -
    SUM(CASE WHEN `Period Type` = 'Last Year' THEN `Value to plot` END))
    /
    SUM(CASE WHEN `Period Type` = 'Last Year' THEN `Value to plot` END)
    


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Chart Filtering

    To remove the possibility of confusion and ensure your chart is displayed properly I recommend filtering in your card only the period types you're attempting to visualize.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Additional Tips

    Future Dates

    If you're not interested in having future dates you can filter those out of your initial offset dataset transform table (dates) if you're using MySQL. For example:

    select d.`dt`
    from `calendar_dates` d
    WHERE d.`dt` <= CURRENT_DATE()
    

    If you're using Magic ETL you can add an initial filter and filter for dates prior to or equal to today.

    HOWEVER! Because of some changes Domo has made to their dataflow processing your dataflow won't run every day because your input datasets don't update or have any changed data daily so it'll be stuck in time when you initially run it. To get around this issue you'll need to add another input dataset to your dataflow that runs and changes daily (I just configured a workbench job to select the current date and time which ran on a daily basis).

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Caveats

    Dates are a tricky thing. If you subtract a month from March 31st you'll get February 28th (or 29th depending on the year... again, dates are tricky) so there is a chance that if you're comparing month over month you might count a day multiple times (March 28->February 28, 29->28, 30->28, 31->28) so it may not be perfect for every situation.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Conclusions

    I hope you enjoyed this walk through and found it useful. Feel free to post any questions or clarifications or even your own tips / tricks!

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Addendum

    Here's a corrected / updated Magic ETL implementation as the prior version had some incorrect offset values.

    {"contentType":"domo/dataflow-actions","data":[{"name":"Domo Dimensions - Calendar","id":"3ea88485-92ab-455d-8e70-118cdd851191","type":"LoadFromVault","gui":{"x":60,"y":18,"color":3238043,"colorSource":null},"dependsOn":[],"removeByDefault":false,"notes":[],"dataSourceId":"d6b99a99-63c7-46f0-8e54-38045e29e710","executeFlowWhenUpdated":false,"onlyLoadNewVersions":false,"columnSettings":{},"visiblePartitionColumn":null},{"name":"Current","id":"66ec18a6-b8dd-4fff-a67d-7195583d1d24","type":"Constant","gui":{"x":168,"y":18,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"Current"}]},{"name":"Current Date","id":"436c9649-202a-4da3-a300-1cdfc37c42db","type":"DateCalculator","gui":{"x":276,"y":18,"color":null,"colorSource":null},"dependsOn":["66ec18a6-b8dd-4fff-a67d-7195583d1d24"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"fieldB":null,"exprB":null,"constantB":{"value":"0","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"DAYS"}}]},{"name":"Last Week","id":"cf47b30d-dd64-4dc3-8be9-0bc7f157efdd","type":"Constant","gui":{"x":168,"y":114,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"Last Week"}]},{"name":"Last Week Date","id":"25e60073-8cae-4ec4-a85a-e72b19c539c9","type":"DateCalculator","gui":{"x":276,"y":114,"color":null,"colorSource":null},"dependsOn":["cf47b30d-dd64-4dc3-8be9-0bc7f157efdd"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"fieldB":null,"exprB":null,"constantB":{"value":"7","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"-DAYS"}}]},{"name":"Last Month","id":"5231a16c-b884-491e-b644-2a3d65efa9ab","type":"Constant","gui":{"x":168,"y":210,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"Last Month"}]},{"name":"Last Month Date","id":"a24b1efd-4f25-49af-aefb-73db6b554ff6","type":"DateCalculator","gui":{"x":276,"y":216,"color":null,"colorSource":null},"dependsOn":["5231a16c-b884-491e-b644-2a3d65efa9ab"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"constantB":{"value":"1","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"-MONTHS"}}]},{"name":"28 Days Ago","id":"a62144ce-10f6-4039-94c6-2e11432f1ef2","type":"Constant","gui":{"x":168,"y":306,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"28 Days Ago"}]},{"name":"28 Days Ago Date","id":"1815477c-0704-4d66-9c21-e48b5c3554be","type":"DateCalculator","gui":{"x":276,"y":306,"color":null,"colorSource":null},"dependsOn":["a62144ce-10f6-4039-94c6-2e11432f1ef2"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"constantB":{"value":"28","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"-DAYS"}}]},{"name":"Last Year","id":"ef158a02-fd86-44d1-99bf-09195c4b4496","type":"Constant","gui":{"x":168,"y":402,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"Last Year"}]},{"name":"Last Year Date","id":"86d4f7ec-1e4e-4e50-91b4-a44099fd2cf0","type":"DateCalculator","gui":{"x":276,"y":402,"color":null,"colorSource":null},"dependsOn":["ef158a02-fd86-44d1-99bf-09195c4b4496"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"constantB":{"value":"12","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"-MONTHS"}}]},{"name":"Last Year DOW","id":"9eaae432-7bc6-49d7-b451-81e58f6265bf","type":"Constant","gui":{"x":168,"y":498,"color":null,"colorSource":null},"dependsOn":["3ea88485-92ab-455d-8e70-118cdd851191"],"removeByDefault":false,"notes":[],"fields":[{"name":"Period Type","type":"STRING","expr":null,"value":"Last Year DOW"}]},{"name":"Last Year DOW Date","id":"7f79d3cf-718c-4bea-9b19-9a257260fc4d","type":"DateCalculator","gui":{"x":276,"y":498,"color":null,"colorSource":null},"dependsOn":["9eaae432-7bc6-49d7-b451-81e58f6265bf"],"removeByDefault":false,"notes":[],"calculations":[{"fieldName":"Comparison Date","calcType":"ADD_SUBTRACT_DATE","fieldA":"dt","exprA":null,"constantA":null,"fieldB":null,"exprB":null,"constantB":{"value":"364","type":"LONG"},"fieldC":null,"exprC":null,"constantC":{"type":"STRING","value":"-DAYS"}}]},{"name":"Append Rows","id":"986ae138-d19f-4f35-81c5-26a888471497","type":"UnionAll","gui":{"x":552,"y":228,"color":null,"colorSource":null},"dependsOn":["436c9649-202a-4da3-a300-1cdfc37c42db","25e60073-8cae-4ec4-a85a-e72b19c539c9","a24b1efd-4f25-49af-aefb-73db6b554ff6","1815477c-0704-4d66-9c21-e48b5c3554be","86d4f7ec-1e4e-4e50-91b4-a44099fd2cf0","7f79d3cf-718c-4bea-9b19-9a257260fc4d"],"removeByDefault":false,"notes":[],"unionType":"INCLUDE_ALL","strict":false},{"name":"Select Columns","id":"4c0c4d70-c9c5-4d7d-ae89-6bf7278e6abf","type":"SelectValues","gui":{"x":647,"y":228,"color":null,"colorSource":null},"dependsOn":["986ae138-d19f-4f35-81c5-26a888471497"],"removeByDefault":false,"notes":[],"fields":[{"name":"dt","rename":"Report Date"},{"name":"Period Type","rename":null},{"name":"Comparison Date","rename":null}]},{"name":"Calendar Dates With Offsets - Magic ETL","id":"1abf7dfe-23c2-4641-8a6d-1744f8d6410f","type":"PublishToVault","gui":{"x":768,"y":228,"color":null,"colorSource":null},"dependsOn":["4c0c4d70-c9c5-4d7d-ae89-6bf7278e6abf"],"removeByDefault":false,"notes":[],"dataSource":{"guid":"f0f185c5-8343-45a2-863b-f1ddd7514ac5","type":"DataFlow","name":"Calendar Dates With Offsets - Magic ETL","cloudId":"domo"},"versionChainType":"REPLACE","partitionIdColumns":[]}]}
    
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • hi, do you mind explaining this step more, I cannot find where to paste the code.

    Thank you

  • hi, do you mind explaining this step more, I cannot find where to paste the code.

    Thank you

  • so just copy that code and open up Magic ETL and press CTRL V to paste. you should now see all the new tiles added to Magic ETL.

  • Greatly appreciated but I don't believe this would solve my issue. I need to be able to key off of the specific date range that a user has entered into the Dashboard Filter. So if for instance the user selected 9/1/2022 thru 8/31/2023, I would generate Summary data for 9/1/2021 thru 8/31/2022. How for instance do we include the "variable" of dates in the filter in a beast mode calculation to generate the prior date ranges transactions? Is it possible?

  • You would create a beast mode for each of set which would be a conditional sum where the period type = Last Year then use that beast mode for the prior year graph and period type = current for this year. Both are keyed off of the report date field but the data would conditionally be last year or current

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Hello @GrantSmith,

    I am working on a request to show last week, last 2 weeks and so on when Date grain variable (last week, last 2 weeks) is selected. I am trying to replicate the steps you mentioned in the thread.

    Question: I have left joined Calendar dataflows Comparison date with my datasets Date. When I select 'Last week' in the variable dropdown all the years containing Period type as Last week gets selected and Sales column is aggregated. If I select last week, only last weeks sales should be populated.

    Please let me know if I am missing something.