Create Index values in time series chart

[#1]

Hi everybody,

I have been trying to find answers to this questions, but haven't come far yet.

For simplicity let's assume that my data has the following columns: year, department and sales.

I want to create a time-series line chart showing the sales developement over years with separate lines for each department.

However, the sales values should be indexed to/divided by the earliest time-series value per department, so that the time series starts with value 1 for each department. If the end user changes the displayed years, the chart should always update so that the values are indexed to the minimum years' sales.

I thought that the following would do the job

=SUM(sales) / Sum({$<year = {$(=Min([year]))}>} Sales)

Yet, this only displays a value at the minimum year in the chart.

Do you have any idea how I could achieve the desired result?

[sunny_talwar]

Try this:

=SUM(sales) / Sum(TOTAL {$<year = {$(=Min([year]))}>} Sales)

[swuehl · Accepted solution]

You could also use chart inter record function for this:

=Sum(Sales) / Top(Sum(Sales))

You probably use two dimensions, Year and Department, but want to use the inter record functions in a virtual table with dimensions in order Department and Year:

Aggr( Sum(Sales) / Top(Sum(Sales)) , Department, Year)

This should work with Year values in chronologic load order, even when a Department does not show same initial year than another one.

[#4]

It's a good idea, but the TOTAL makes QV take the sum over all departments, while I would actually need the sum for each department separately.

Do you happen to know if I can apply the TOTAL only to the year and still let the expression Sum(TOTAL {$<year = {$(=Min([year]))}>} Sales) differentiate by department?

[#5]

Thank you, this solution is working really well.

Yet, it is also a bit cumbersome, since the load order has to be chronological. What if I had differently sorted dimensions and wanted to use them in different charts. What I am still trying to figure out is how to make the value

Sum({$<year = {$(=Min([year]))>} Sales) constant (i.e. not depend the year dimension in the chart) while Sum(Sales) depends on the year dimension.