Hi Everyone,
I'm coming across a fun thing. Our Advancement team has put together a dashboard comparing our Year End Campaign comparing this year's campaign to prior years. We have a bar graph showing contributions as of year to date and a line graph showing progression of both current and year to date for prior.
The problem we're coming across is the bar graph will display as of today and the line graph appears to show as of the day prior. I understand that this is due to no current contributions have been processed for the current day so the line does not progress. I'd be happy to either offset the bar graph to show the day prior amounts or to continue the line on the line graph to continue to the current day of the year. Would either be better or easier to do than the other? I'm at a loss to get this done.
I'm currently using this script to continue the graph without breaks:*/widget.on("beforeviewloaded",function(scope, args){args.options.plotOptions.series.connectNulls = true;}); widget.on('render', function(sender,se){for (var i = 0; i < sender.queryResult.series.length; i++ ) { for (var j = 0; j < sender.queryResult.series[i].data.length; j++) { sender.queryResult.series[i].data[j].marker.enabled = false; } }})
In the screen shot, the red line on the line graph is where the current line should be.
I can manually filter based on value of the Calendar Day of the Week being less than 346, but I'd much rather not have to manually change that value each day. Here is a screenshot of the charts with that manually edited filter in place.
Thank you for your insights. I'm sure it's probably a simple answer I'm just not thinking of.
Hey Daniel,
I could be wrong, but you just need to use a combination of techniques Bucketing and math. I'd suggest you make a duplicate of your widget and then convert it to a pivot table. This will allow you to confirm the results that you want to show.
You'll then create a separate value for each "bucket" (FY18 Year End Campaign, FY19..., FY20...). You'll use the Filtered Value concept to make each bucket.
For example, this will show the credit amount filtered only to a specific payment method. (SUM([Credit Amount]), [Payment Method]).
The syntax is (<Aggregation value>, <Filter1>, <Filter2>, <FilterN>)
Here is a bucketing webinar:
https://www.tessituranetwork.com/Items/Videos/Training/Analytics/Webinar-Collection?_ga=2.11766324.1429965827.1647986670-1374917692.1647986670
Once you have the separate buckets, you'll then need to adjust your RSUM formulas to subtract the current day's amount only on the past FYs.
I use this concept to track our gift certificate "revenue". In my formula, I use filtered values and have it subtract the credit amount for a specific payment method with at Transaction Date = to today's date last year.
- (SUM([Credit Amount], [Payment Method = *GC], [Today's Date Last Year])
The [Today's Date Last Year] part I did by right-clicking on that part, Filter, Time Frame, Chose Days, Next 7. I then click the Advanced button and change the count to 1 and offset = -365. Once I press the Test button, it shows today's date last year (12/13/21 since today is 12/13/22). This confirms that it will subtract the credit amount for today's date last year from the total.