How to add pivoted value to new columns by Magic ETL

eriena

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.

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?

Thank you very much.

MarkSnodgrass

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

MarkSnodgrass

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

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

GrantSmith

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

eriena

@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

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

eriena

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

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.

Could you advise what was wrong to this code?

Thank you very much for your kindly help.

MarkSnodgrass

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

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

eriena

@MarkSnodgrass

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

Thank you very much for your help.