How can I group multiple columns by year-month in the dataflow?

Reply
Highlighted
Yellow Belt

How can I group multiple columns by year-month in the dataflow?

So I have a dataset that looks kind of like this:

 

Date.                  A   B
March 2, 2020          2   10
March 14, 2020         4   20
Feb 12, 2020           3   8
Feb 21, 2020           1   4
Jan 3, 2020            6   10
Jan 17, 2020           3   5

I'd like group the rows by year-month essentially stripping the day from the date column while simaltaneously summing the combined rows within that month. Should look something like this:

Date      A   B
2020-3 6 30
2020-2 4 12
2020-1 9 15

The very end goal is to create a Simple Moving Average Calculation with a 3 month window for each month that averages the values of the summed up columns

Thank you so much in advanced!


Accepted Solutions
Highlighted
Black Belt

Re: How can I group multiple columns by year-month in the dataflow?

Yes, you can do this in the ETL by grabbing the month and year from a date column and then re-construct a date column as the first of the month. Here is a screenshot of a sample dataflow that shows the steps and then I will explain what is going on in each step below.

dateflow.PNG

Date Operations: Add a column called Month and use the Month of Date function. Also add a column called Year and use the Year of Date function.

Add Contants: Add a column called Day and add a Whole Number field with a value of 1.

Combine Columns: Choose Other as the separator and combine the month, day and year columns, in that order.

Set Column Type: Set your newly created column to a data type of date with month first.

Select Columns: Remove and rename any columns.

 

This should get you a column that will have all dates as the first of the month that they occurred in.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Highlighted
Black Belt

Re: How can I group multiple columns by year-month in the dataflow?

If you go to date range filter in the card analyzer, you can tell it to group by month. This will group it by month and year and automatically the sum the totals for you. This KB article may help you.
https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/KPI_Card_Building_Part_2%3A_The_...

 




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Tags (1)
Highlighted
Yellow Belt

Re: How can I group multiple columns by year-month in the dataflow?

Would it be possible to do this in the ETL? Because I'm going to need to perform the simple moving average calculation right after I group the rows together and I'm assuming it's not possible to do that part in the card analyzer right?

Highlighted
Black Belt

Re: How can I group multiple columns by year-month in the dataflow?

Yes, you can do this in the ETL by grabbing the month and year from a date column and then re-construct a date column as the first of the month. Here is a screenshot of a sample dataflow that shows the steps and then I will explain what is going on in each step below.

dateflow.PNG

Date Operations: Add a column called Month and use the Month of Date function. Also add a column called Year and use the Year of Date function.

Add Contants: Add a column called Day and add a Whole Number field with a value of 1.

Combine Columns: Choose Other as the separator and combine the month, day and year columns, in that order.

Set Column Type: Set your newly created column to a data type of date with month first.

Select Columns: Remove and rename any columns.

 

This should get you a column that will have all dates as the first of the month that they occurred in.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Highlighted
Red Belt

Re: How can I group multiple columns by year-month in the dataflow?

you CAN do a lag function in Analyzer!

 

Youtube video explanation:  https://youtu.be/cnc6gMKZ9R8

Capture.PNG

lag(sum(`Measure1`)) over( order by year(`Date`) * 100 + WEEKOFYEAR(`Date`) )


Jae Wilson
Check out my Domo Training YouTube Channel

**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"
Technical Success Manager Jae Wilson shares how to create lag functions in Card #Analyzer using #BeastModes https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/...
Highlighted
Green Belt

Re: How can I group multiple columns by year-month in the dataflow?

@jaeW_at_Onyx  thank you much for this video and for the great explanation on why you would prefer not to do this via ETL.

 

How do I get the lead/lag functions?  When I attempt to call them via beast mode I'm getting 'Invalid Formula : This calculation is using a nonexistent function.'

 

Thank you,

Highlighted
Red Belt

Re: How can I group multiple columns by year-month in the dataflow?

Sorry, I thought I replied. 

Ask your CSM to enable 'window functions in beast modes'


Jae Wilson
Check out my Domo Training YouTube Channel

**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"
Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.