Connecting Monthly Goals to Incoming Cumulative Daily Data

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.

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