2 Replies Latest reply: Nov 6, 2015 9:41 AM by Frank Casella RSS

    Line Chart with multiple trend lines

    Frank Casella



      Still a bit of a novice and struggling to create a line chart that shows trendlines for the top 10 elements within a dimension and for all elements within the dimension (total trendline).  I've managed to do both, but can't seem to get them into the same chart. The charts are displaying the return rates for each of the companies to which we sell product.


      For the "top 10 elements", I'm using dimensions of [Quarter Name] and [Company Name], showing the top 10 companies by 2015 sales:

      Dimension 1: =[Quarter Name]

      Dimension 2: =IF(AGGR(RANK(SUM({<[YEAR]={2015}>}SALES)),COMPANY_NAME)<11,Capitalize(COMPANY_NAME),'Not in Top 10')

      Expression: Sum({<[RETURN_FLAG]={1}>}SALES)/Sum(SALES)

      I'm setting the equation properties to "Invisible", "Use Trendlines" > "Exponential"


      To get the "all elements", I'm simply duplicating the chart and deleting dimension 2.


      I've tried to manually create the trendlines using linest_m and linest_b (creating linear trend lines instead of exponentials for now) with the following:

      =Linest_M(Total Aggr(Sum({<[RETURN_FLAG]={1}>}SALES)/Sum(SALES),[Quarter Name]),[Quarter Name])*[Quarter Name]+Linest_B(Total Aggr(Sum({<[RETURN_FLAG]={1}>}SALES)/Sum(SALES),[Quarter Name]),[Quarter Name])


      This also works to display the total trendline, but won't display a trendline for each company name. I'm guessing I could explicitly define the company names within the set analysis, adding it within the brackets with the return flag.  This would require sacrificing the ranking requirement, and need to be updated manually each quarter (unless the top ten didn't change).


      I tried overlaying the two charts, but this was very cumbersome and looks wonky because I can't seem to get the x-axis scaling to match.  Any ideas?


      Thanks for the help!

        • Re: Line Chart with multiple trend lines
          Srikanth P

          Frank, firstly you can't combine these two charts into one line chart the reason is one chart have max of 11 company names and other chart needs to show trend line for each company name.


          In the second chart you don't have 2nd dimension as Company Name but you need to show trend line for each company name. So in the second chart add Company name as 2nd dimension.


          If you want combine two charts, you may want slightly change the requirement like create calculated dimension like CompanyName - Top 10 or CompanyName - Not in Top 10.

          • Re: Line Chart with multiple trend lines
            Frank Casella

            Thanks for the info.  I'll try modifying the second chart by adding the second dimension and see if this allows me to overlay the charts a bit easier.  The idea for combining the charts by changing the requirements is less desirable, as the request was to show trending for the top 10 companies as it compared to overall trends.