Cummulative Total With Inbuilt Dimension Filter (Rangesum with If?)

Thu, 27 Feb 2014 10:26:14 GMT

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

Re: Cummulative Total With Inbuilt Dimension Filter (Rangesum with If?)

marcus_sommer — Thu, 27 Feb 2014 13:43:25 GMT

Look here for an example to cumulative data within a chart.

- Marcus

Re: Cummulative Total With Inbuilt Dimension Filter (Rangesum with If?)

Thu, 27 Feb 2014 14:05:49 GMT

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

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

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

topic Re: Cummulative Total With Inbuilt Dimension Filter (Rangesum with If?) in QlikView

Cummulative Total With Inbuilt Dimension Filter (Rangesum with If?)

Re: Cummulative Total With Inbuilt Dimension Filter (Rangesum with If?)

Re: Cummulative Total With Inbuilt Dimension Filter (Rangesum with If?)

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