Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have racked my brains with this one and figure it's time I asked the forum experts for help! I am trying to produce a line graph that shows cumulative sales against cumulative budget (which I can achieve using the rangesum function). However, I want to build in a dimension restriction for which weeks are shown in the graph without the user filtering manually. Here is some example data together with the two styles of graph I would like to achieve:-
WeekNo | Sales | Budget | Cumm Sales | Cumm Budget | Req'd Sales | Requ'd Budget |
---|---|---|---|---|---|---|
1 | 100 | 75 | 100 | 75 | 100 | 75 |
2 | 200 | 250 | 300 | 325 | 300 | 325 |
3 | 50 | 80 | 350 | 405 | 350 | 405 |
4 | 75 | 80 | 425 | 485 | ||
5 | 100 | 425 | 585 | |||
6 | 150 | 425 | 735 | |||
7 | 50 | 425 | 785 | |||
The first graph I want to produce merely limits the weeks displayed to a specific max point, already calculated in a separate variable (vMaxWeek), which in this example is WeekNo 4. The second graph I want to produce covers the entire sales season (up to vMaxSeasonWeek) showing the cumulative budget for the whole season, but actuals only for the part season (to vMaxWeek) - otherwise it looks like sales are forecast to flatline for future weeks.
Note - In my actual examples the time period has a lower limit too that I also want to auto-restrict that links to the start of the season (vStartSeason).
I attach an excel file with the data above and examples of the two graphs I'd like to produce. Any help/suggestions would be hugely appreciated. This has hit the limit of my QV skills!
Many thanks
Vivienne
Look here for an example to cumulative data within a chart.
- Marcus
Hi Marcus,
Many thanks for your reply. Unfortunately it's not the cumulative total I can't achieve, it's producing it at the same time as limiting the primary dimension that's the issue.
All the Best
Vivienne