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

user14700

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!

MarkSnodgrass

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.

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.

MarkSnodgrass

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_Analyzer/045Adding_Filters_to_Your_Chart#Changing_the_unit_of_time_used_to_represent_data_(.22date_grain.22)

 

user14700

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?

MarkSnodgrass

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.

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.

jaeW_at_Onyx

you CAN do a lag function in Analyzer!

 

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

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

JasonAltenburg

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

jaeW_at_Onyx

Sorry, I thought I replied. 

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

crispinvaldez

Hello, 

 

Have the lead and lag functions been turned off since this post? I am being told we have window functions on but lead and lag are not working. 

jaeW_at_Onyx

This'll sound snarky but isn't meant to be.  Did you try it?

 

Yes.  Lead and Lag still work.

 

Domo is a phenomenally powerful platform that is not completely documented because groundbreaking changes come out every quarter.  I strongly recommend that you get used to both trial and error and googling your question.

 

A year and some ago, Domo opened up their site to Google's search engine, making Google the best way to search for information about Domo (as opposed to using the built-in search which is ... subpar IMHO.

crispinvaldez

No worries Jae!

I did try it and I am getting the same error Jason is above. I actually got a reply from the Domo support team and they told me they have never been part of beast modes. I will continue trying and trialing it. I agree that Google is an excellent research tool for things like this. That's actually how I found you and your Youtube channel. Which thank you very much for your videos. I have watched quite a few. 

 

Thank you! 

jaeW_at_Onyx

aww geeze @crispinvaldez , now i feel like a jerk.

 

I did test out LAG() in my instance of Domo before replying and I did get a result that with 5 seconds of examination looked correct.

 

In general Domo tries to avoid sunsetting features that would impact users, so if someone told me "it worked yesterday but broke today because Domo got rid of it." I'd raise eyebrows. 

 

post your beast mode in Dojo.  You must have all the right parts.

 

lag(sum(`Measure1`), <optional offset> ) over( order by year(`Date`) * 100 + WEEKOFYEAR(`Date`) )

 

so LAG() has to be over an aggregate function SUM, MIN MAX etc.

 

In the OVER() clause you must have an ORDER BY clause && the expression must be the same as the GROUP BY clause in your overall query, so in my case, I MUST have Year(date)... in the order by clause b/c that's included in the axis of my table.

 

 

crispinvaldez

I am not sure why its not working and I know window functions are turned on. 

 

lag(SUM(`Cost`)) over (order by YEAR(`Date`) * 100 + WEEKOFYEAR(`Date`))

jaeW_at_Onyx

@crispinvaldez I don't know what to tell you.  Email this screenshot to Chuck and ask him to validate that Windowed functions are enabled on your instance of Domo.

JaredUJZ

@crispinvaldez, I'm not sure if you're still struggling with this, but I couldn't get it either. Then someone else helped me and got it to work. The only difference was that he put a space after "lag" and before the parenthesis and also after "over".

crispinvaldez

Thank you so much!! It was that simple. That worked!

EternalShima

Thank you @jaeW_at_Onyx  for the solution in beast mode.

I am trying to calculate the rolling average at the country level (about 200 countries)

For the average, I used the following code in beast mode :

(

 lag(COUNT(`SEG_Mktg` in 'Yes'),1) over(PARTITION by `Country` order by MONTH(`Snap`))+

 lag(COUNT(`SEG_Mktg` in 'Yes'),2) over(PARTITION by `Country` order by MONTH(`Snap`))+

 lag(COUNT(`SEG_Mktg` in 'Yes'),3) over(PARTITION by `Country` order by MONTH(`Snap`))

) / 3

I receive strange numbers, with mentioned code and so I should do calculation other way

I would be glad to hear your suggestion, maybe for this, I should do in Magic ETL and then run average over lag columns