Connecting Monthly Goals to Incoming Cumulative Daily Data

EChampagne

I'm trying to create a card that shows cumulative daily sales against a monthly goal. The data is coming from 2 different sources - the daily info from our raw data, and the goals I was going to input as a webform to then connect with daily data. How do I get the "month" from my webform to match the cumulative raw daily data?

david_cunningham

You'll need to aggregate your raw data up by month/year in an ETL and then do a join on month/year and whatever your goal descriptor is.

Another option is to use a date table and a cross join with your webform to generate a date for each monthly goal, divide the monthly total by the number of days in the month to get your daily goal. You can then join by day and goal to your raw data. Making sure to aggregate your raw data by day before joining.

A third option that would eliminate the need for a join. Is to make use of a running total bar or line chart, and then simply plot a goal line on that with your goal value. Once the running total line crosses, you have hit your goal. This option gives you the most flexibility as you don't have to pre-aggregate your data at all. Would really only work though if your monthly goal is consistent. Otherwise will require you to go in month to month and update.

If monthly goal is variable. I'd recommend option 2. There is a way to avoid aggregating by day. You'll have to make use of window functions and average the goal and sum the actual. Doing this type of join though can cause you to have duplicate rows of data that you'll need to be very careful dealing with.

ArborRose

You will need to make sure there's a connection between the data sources. Something in common you can use to join the sets. Your raw data would be showing something like a date and sales amount.

Date, Sales
2024-01-01, 1200.00
2024-01-23, 900.00
2024-02-14, 2400.00

And your month goals might be something like

Date, Goal
2024-01, 50000
2024-02, 45000
2024-03, 62000

Then merge the sets based on the month and year.

You can extract month from a full date. So if you wanted to you could use end of month dates for your goals. Something like 2024-01-31 would still represent month by taking MONTH('2024-01-31'), or monthname.

ArborRose

If you have a column such as a date in your table for goals, you can identify the quarter the same way you can pull month - QUARTER(mydate). Quarter being 1,2,3, or 4.

When I configure my primary transaction flow, I have a column pre-calculated for each of year, quarter, month, and week. That way those values are readily available to me.

ArborRose

As David notes - you will need to aggregate ("group" and sum/count), with necessary fields. If you add fields to an output, the data will "break" or split on that data. Example, if your objective is to sum the amounts by store then you might include Store, Quarter, Month, and Sales. Since Month is in the set, it will split the data on each month within the quarter. If you remove month but keep the other fields, it will give you Store, Quarter, and Sales - total by quarter. If you add a salesperson in the data, it will split up on that as well.

And its up to you whether you are using an ETL with tiles, or doing things in beast mode formulas.

If I use formulas to aggregate what I need - I'll split the data up with additional columns for year, quarter, month, or week. That way the values are there and I can choose what type of totals I want on the display.

You can chart past data or future data. For current year and current quarter, I might have a formula such as:

sum(
case when YEAR(mydate) = YEAR(CURRENT_DATE())
and QUARTER(mydate) = QUARTER(CURRENT_DATE())
then amount else 0 end
)

And for a previous year, current quarter:

sum(
case when YEAR(mydate) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and QUARTER(mydate) = QUARTER(DATE_ADD(CURRENT_DATE(),-365))
and mydate <= DATE_ADD(CURRENT_DATE(),-365)
then amount else 0 end
)

You can use static values as well. For first quarter, you might use:

sum(
case when YEAR(mydate) = YEAR(CURRENT_DATE())
and QUARTER(mydate) = 1
then amount else 0 end
)

Even though its only the second quarter of the year, I can chart data for the entire year if I have those values in my data. Perhaps future appointment dates are in the data and I want to see the number of appointments in the third quarter of the current year, I can say QUARTER(mydate) = 3

david_cunningham

You'll need to aggregate your raw data up by month/year in an ETL and then do a join on month/year and whatever your goal descriptor is.

Another option is to use a date table and a cross join with your webform to generate a date for each monthly goal, divide the monthly total by the number of days in the month to get your daily goal. You can then join by day and goal to your raw data. Making sure to aggregate your raw data by day before joining.

A third option that would eliminate the need for a join. Is to make use of a running total bar or line chart, and then simply plot a goal line on that with your goal value. Once the running total line crosses, you have hit your goal. This option gives you the most flexibility as you don't have to pre-aggregate your data at all. Would really only work though if your monthly goal is consistent. Otherwise will require you to go in month to month and update.

If monthly goal is variable. I'd recommend option 2. There is a way to avoid aggregating by day. You'll have to make use of window functions and average the goal and sum the actual. Doing this type of join though can cause you to have duplicate rows of data that you'll need to be very careful dealing with.

ArborRose

You will need to make sure there's a connection between the data sources. Something in common you can use to join the sets. Your raw data would be showing something like a date and sales amount.

Date, Sales
2024-01-01, 1200.00
2024-01-23, 900.00
2024-02-14, 2400.00

And your month goals might be something like

Date, Goal
2024-01, 50000
2024-02, 45000
2024-03, 62000

Then merge the sets based on the month and year.

You can extract month from a full date. So if you wanted to you could use end of month dates for your goals. Something like 2024-01-31 would still represent month by taking MONTH('2024-01-31'), or monthname.

EChampagne

Thank you, both.

A follow-up question, since I have goals through the quarter - will all of these methods show the goal for future months in the quarter? I'm not sure I've seen 'forward looking' data yet.

ArborRose

If you have a column such as a date in your table for goals, you can identify the quarter the same way you can pull month - QUARTER(mydate). Quarter being 1,2,3, or 4.

When I configure my primary transaction flow, I have a column pre-calculated for each of year, quarter, month, and week. That way those values are readily available to me.

ArborRose

As David notes - you will need to aggregate ("group" and sum/count), with necessary fields. If you add fields to an output, the data will "break" or split on that data. Example, if your objective is to sum the amounts by store then you might include Store, Quarter, Month, and Sales. Since Month is in the set, it will split the data on each month within the quarter. If you remove month but keep the other fields, it will give you Store, Quarter, and Sales - total by quarter. If you add a salesperson in the data, it will split up on that as well.

And its up to you whether you are using an ETL with tiles, or doing things in beast mode formulas.

If I use formulas to aggregate what I need - I'll split the data up with additional columns for year, quarter, month, or week. That way the values are there and I can choose what type of totals I want on the display.

You can chart past data or future data. For current year and current quarter, I might have a formula such as:

sum(
case when YEAR(mydate) = YEAR(CURRENT_DATE())
and QUARTER(mydate) = QUARTER(CURRENT_DATE())
then amount else 0 end
)

And for a previous year, current quarter:

sum(
case when YEAR(mydate) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
and QUARTER(mydate) = QUARTER(DATE_ADD(CURRENT_DATE(),-365))
and mydate <= DATE_ADD(CURRENT_DATE(),-365)
then amount else 0 end
)

You can use static values as well. For first quarter, you might use:

sum(
case when YEAR(mydate) = YEAR(CURRENT_DATE())
and QUARTER(mydate) = 1
then amount else 0 end
)

Even though its only the second quarter of the year, I can chart data for the entire year if I have those values in my data. Perhaps future appointment dates are in the data and I want to see the number of appointments in the third quarter of the current year, I can say QUARTER(mydate) = 3