Week over week metrics

stalcott
stalcott Member
in Archive

Hi,

I want to show Week over week metrics (Column) in my dataset.

 

I have used date ranges at weekly level and showing visits metric in another column in a bar chart.

Now, I want to add a line showing Week over week variation.

 

Domo 1.PNG

 

 

I have read the other posts/solutions and know it has to be with a self join only and within Magic ETL - but was facing some issues in implementing the same. 

The closest was this post in the last answer by McSQL

 

Any approach/feeback/examples would be highly appreciated.

 

Comments

  • AS
    AS Coach

    Similar to McSQL, I've done the same thing but flattened: join the data on itself but shifted by a week (or month, or whatever the business need is), then output "Vists Last Period" as a new column next to "Visits This Period".  Seemingly identical to the first dataset but with the new last period column.

     

    Then your % change beast mode looks something like:

    (`Visits This Period` - `Visits Last Period`) / `Visits Last Period`

     

    Your card would then be built with the date as the X Axis, your "Visits This Period" as the first Y Axis and your % change beast mode as the first series (second value on Y Axis).  Furthermore, you could do three metrics (which is what I did).  One symbol for % change, one bar for this period, and one bar for last period.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • stalcott
    stalcott Member

    @AS Thanks!

     

    I was just trying this self join and for shifting the data by a day - I am trying to create a new column "Date 2" which is equal to the "Actual date + 1" and then join this with the original dataset on Date only.

     

    This will give me original Visits and one day prior visits, which can then be used to get Week over week metrics in card building.

     

    However, I am just not able to create "Date 2" column and found no posts with creation of new calculated columns in datasets (except constants) - without sql use. 

    Also, is there any way to convert data to weekly and get WoW metrics in magic ETL only?

     

    Any approach/suggestions will be highly appreciated!

     

  • AS
    AS Coach

    If your data is by transaction, I'd first aggregate it to daily by whatever dimensions you need.  Then do a "Date Operations" column to create a new data, taking your date column and adding negative days (like -7 for last week's data).  Then add a join transform to join the "daily aggregate" and the "daily aggregate with prior date" transforms, using the date and dimension columns as your join conditions.  Then you have an output dataset that, for every dimension combination, has a column for daily data as well as the prior time period's daily data.

    See attachments for example.  I always do this by SQL dataflow but you might try something like what I've shown in the attachments.  It should give you some ideas, at least.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • [Deleted User]
    [Deleted User] Member

    There is also a good article in our Help Center on this topic.

    Regards,

     

  • AS
    AS Coach

    That may not be an article everyone has access to, @DaniBoy.

    But I wouldn't be surprised if some kind of PoP approach is documented somewhere.

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • [Deleted User]
    [Deleted User] Member

    Thanks @AS!

     

    The Help center is now accessile to all customer who have a Domo license. It can be accessed easily from the top right of any Dojo page or by going to the following url: https://knowledge.domo.com/.

     

    Domo customers will need to use their Domo login credentials to access the wealth of articles.

     

    Regards,

    Dani

     

     

  • AS
    AS Coach

    @DaniBoy, this is what I see when I click on your link and I notice the URL says it's a beta feature:

    No access.JPG

     

    Thre is also this article in the knowledge base, which goes over the SQL version: 

    https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Period_over_Period

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • [Deleted User]
    [Deleted User] Member

    Thanks @AS!

     

    I updated the thread and this is the better link.

     

    https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Period_over_Period

     

    Let me know if this works ok.

     

    Regards,

    Dani

This discussion has been closed.

Categories