6 Replies Latest reply: Dec 5, 2016 3:25 AM by x mg RSS

    Add the aggregate line to a line chart with multiple lines

    Jerry Xie

      Dear all.

       

      I have a following data :

      Category  Sub category  Month  Price  Sales

      A               A1                    1       100         10

      A               A1                    2       100         10

      A               A1                    3       100         10

      A               A1                    4       100         10

      A               A1                    5       100         10

      A               A1                    6       100         10

      A               A2                    1       100         10

      A               A2                    2       100         10

      A               A2                    3       100         10

      A               A2                    4       100         10

      B               B1                    1       100         10

      B               B1                    2       100         10

      B               B1                    3       100         10

      B               B1                    4       100         10

      B               B1                    5       100         10

      B               B1                    6       100         10

      B               B2                    1       100         10

      B               B2                    2       100         10

      B               B2                    3       100         10

      B               B2                    4       100         10

      C               ......................................................

       

      I don't want to hard code the expressions, since I'd like the user to be able to select the category  names and then display the results accordingly. The line chart is monthly basis.If user chose A Category, the chart will show three monthly trend lines, one is for A1 monthly price trends, one is for A2 monthly trends and the third one is aggregation line. The logic for aggregation is sumproduct(price,sales)/total sales.

       

      Appreciate if any ideas.

       

      Thanks!!!