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

    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
          Sunny Talwar

          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
              Stefan Wühl

              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.