How to add pivoted value to new columns by 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.
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.
Best Answers
-
@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.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
It looks like you are missing a comma in the function syntax. It should be:
DATE_SUB(`Year/Month` , INTERVAL 12 MONTH)
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
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!**2 -
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.
0 -
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.
0 -
1
Categories
- 11K All Categories
- 5 Private Company Board
- 2 APAC User Group
- 12 Welcome
- 42 Domo News
- 9.9K Using Domo
- 2K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 349 Workbench
- 267 Domo Best Practices
- 11 Domo Certification
- 474 Domo Developer
- 51 Domo Everywhere
- 110 Apps
- 725 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 29 お知らせ
- 65 Kowaza
- 301 仲間に相談
- 662 ひらめき共有