Hi All,
I've got a line chart that shows drama school sales for the current fiscal and is getting to be quite long and not very useful for seeing sales trends. I would like to change it so that it just shows the last 30 days of sales trends. However, if I filter on orders in the last 30 days it doesn't show the proper total.
This is the full chartThis is the chart with a last 30 days of orders filterOther ideas for how I can make this more visually useful? We will continue to add more seasons as time goes on for Winter and Spring classes, so it's only going to get harder to read.
Hi Kanani,
The zoom feature, which does reset when the dashboard is reloaded, only appears with the widget is not big enough to show the full result set without some kind of compression in the axis labels. In other words, if the widget seems big enough to display everything, it doesn't offer the slider/zoom control.
I recommend going ahead with the filter on Last 30 Days, and to use that in combination with a formula that overrides the Last 30 Days and gets the total from prior to that period, and then adds that to every value from the RSUM() result.
RSUM( [Total Ticket Paid Amount] ) + ( [Total Ticket Paid Amount] , [Performance Date Last 360 Days, offset 30] )
Chris, I think conceptually you are correct. However, it depends on what they are actually attempting to display. I think we both are assuming that they want to display the running sum of the entire run $ amount, but only display those running sum totals for the last 30 days. If that is correct, then this is just a simple problem of RSum( X + Y). We just need to figure out how to properly filter the data.
I think your formula would work with the following combination:
1. Use your version on the Formula (Values) levelRSUM( [Total Ticket Paid Amount] ) + ( [Total Ticket Paid Amount] , [Performance Date Last 360 Days, offset 30] )
This results in adding the RSum of money > 30 days plus the RSum of the entire run. This will result in a number too large. To offset this they will need to.
2. Add a Widget level filter to only the last 30 days.This results in the "[Total Ticket Paid Amount] ) +" part of the equation only adding the last 30 days money to the > than 30 days money.
For example, if I used this much simplified example data and I only wanted to display the last 5 dates but use the RSum for the entire run:
We would need to add 28 to every days running sum to correctly display the running sum of the entire run, but only display the last 5 days in the visual.By using the combination of a Widget level filter and a formula filter, it allows us to correctly display what we want (or at least what I think they want).Does that make sense? Anyway, this is how I simplify a complex situation. Basically, what do I need to add/subtract in order to get the correct amount? Then figure out the combination of filter levels so that it gets me the correct numbers. I also always do this using pivot table data first so that it helps me see what to add/subtract.
Hi Neil,
Great recommendations in how to break down formulas. I do want to clarify one thing:
While this is true, the formula I posted was different. It was the RSum of money > 30 days plus the total ticket count on all order dates prior to 30 days ago. So not RSUM( X + Y) but RSUM(X) + Y. This bit returns the same total from all order dates prior to 30 days ago to every row:
( [Total Ticket Paid Amount] , [Performance Date Last 360 Days, offset 30] )
Prior Ticket Count in the Last 30 Days widget of screenshot below. And is confirmed by a running sum widget of all order dates in the Last 360 Days offset 30.
Now we have the same starting value for each row in the Last 30 Days widget to which we can add the RSUM(X).
Thanks for the clarification Chris. My bad, I made an incorrect assumption. You mean there is more than one way to get correct the answer? I also like how you confirmed you were correct. Using a pivot table to confirm numbers is a very useful method.