How to add pivoted value to new columns by Magic ETL

eriena
eriena Member
in Magic ETL

Hello,

I would like to ask how to add pivot value to new column by Magic ETL.

I prepared the table as follows which have value of product for 2021 Apr-Jun and previous year of same month.


image.png


I want to add rows and columns highlighted in yellow as shown on the right table by Magic ETL.

The rows that I want to add is 2021 Apr, May, June,

The columns that I want to add is the total of the columns of "pcs" for Same month of the previous year of 2021 Apr, May, June.

I think it seems that it can be done with ranking & window, but I couldn't set. Please advice how to set it concretely?

image.png


Thank you very much.

Tagged:

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @eriena It looks like you want to show the totals for each month for the previous year in addition to all the detail. You can with just a few steps in Magic ETL:

    • Use a group by tile to group your totals by month
    • Add a formula tile and use the DATE_SUB() function to create a new column that would have the previous years date. It would look like this: DATE_SUB(`dt`, INTERVAL 1 YEAR)
    • Add a join tile and join the formula tile to the previous group by tile and join using the newly created date column and the date column from the group by tile. Rename the total column field from the formula tile side and call it last year total (or something like that). Now you will your previous year total for that month next to the current total.
    • Add an append rows tile and connect your original input dataset and the last join tile to the append tile and include all columns.


    This should give you what you are looking for.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    It looks like you are missing a comma in the function syntax. It should be:

    DATE_SUB(`Year/Month` , INTERVAL 12 MONTH)

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @eriena

    it looks like you’re wanting to do a offset year over year. I’d recommend using a custom date dimension to calculate the values for the prior year or whatever offset you’re wanting. You can read more about how to do this here: https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

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

    @GrantSmith

    Thank you for your response.

    My explanation was not good, I'm sorry. I don't need to offset.

    I have a card I want to make, and if I have the following Dataset( with the rows and columns highlighted), I will be able to make a card.

    Therefore, I would like to know how to create the following Dataset.

    I have not read the URL that you gave me yet.

    First of all, I will read your information and try whether I can make the dataset.

    Thank you very much.

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @eriena It looks like you want to show the totals for each month for the previous year in addition to all the detail. You can with just a few steps in Magic ETL:

    • Use a group by tile to group your totals by month
    • Add a formula tile and use the DATE_SUB() function to create a new column that would have the previous years date. It would look like this: DATE_SUB(`dt`, INTERVAL 1 YEAR)
    • Add a join tile and join the formula tile to the previous group by tile and join using the newly created date column and the date column from the group by tile. Rename the total column field from the formula tile side and call it last year total (or something like that). Now you will your previous year total for that month next to the current total.
    • Add an append rows tile and connect your original input dataset and the last join tile to the append tile and include all columns.


    This should give you what you are looking for.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • eriena
    eriena Member

    @MarkSnodgrass

    Thank you for understanding what I want to do, despite my lack of explanation.

    I tried the setting following your advice to my ETL, then I succeed to group by.

    image.png


    But I couldn't success Add Formula due to my DATE_SUB() function.

    I wrote DATE_SUB(`Year/Month` INTERVAL 12 MONTH) in Add formula box and failed.

    image.png

    Could you advise what was wrong to this code?

    Thank you very much for your kindly help.

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    It looks like you are missing a comma in the function syntax. It should be:

    DATE_SUB(`Year/Month` , INTERVAL 12 MONTH)

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • eriena
    eriena Member

    @MarkSnodgrass

    I was able to make my ETL thanks to your advice!

    Thank you very much for your help.

Categories