A more flexible way to do Period over Period comparisons

GrantSmith

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.

GrantSmith

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

GrantSmith

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

GrantSmith

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

GrantSmith

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

GrantSmith

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":[]}]}

GrantSmith

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:

GrantSmith

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)

GrantSmith

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.

GrantSmith

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).

GrantSmith

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.

GrantSmith

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!

GrantSmith

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":[]}]}

German_Rodriguez

https://dojo.domo.com/main/discussion/comment/56263#Comment_56263

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

Thank you

German_Rodriguez

https://dojo.domo.com/main/discussion/comment/56263#Comment_56263

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

Thank you

Viet

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.

Scott_Gius

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?

GrantSmith

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

Hazel

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.