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

    Create Index values in time series chart

    A B

      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
              A B

              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
                  A B

                  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.