4 Replies Latest reply: Mar 24, 2016 12:33 PM by A B

# Create Index values in time series chart

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?

• ###### Re: Create Index values in time series chart

Try this:

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

• ###### Re: Create Index values in time series chart

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?

• ###### Re: Create Index values in time series chart

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.

• ###### Re: Create Index values in time series chart

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.