4 Replies Latest reply on Aug 2, 2017 4:58 PM by Abdul Subhan Mohammed

    Line Color Based on Increasing or Decreasing trend

    Abdul Subhan Mohammed

      Hi All,

       

      I wanted to create a line where if the sales of present month is higher than previous month, then WHOLE line of that trend should be Green

       

      and

       

      If the sale of present month is lower than previous month, then the WHOLE line should be Red.

       

      I have attached the sample store file where I have been looking for answers.

       

      Anything even close, which you have used in your experience is also helpful.

       

      Thank You

        • 1. Re: Line Color Based on Increasing or Decreasing trend
          Ben Neville

          Try something like this:

          IF WINDOW_MAX(IF SIZE() = INDEX() THEN SUM([Sales]) END) >= WINDOW_MAX(IF SIZE()-1 = INDEX() THEN SUM([Sales]) END)

          THEN "Higher"

          ELSE "Lower"

          END

           

          Place that calculation on color, and assign red and green as appropriate. The output can be a number or whatever text you want - I just picked higher and lower. The trick is to get a value that returns the same for the whole line, so we need a table calculation (though an LoD would work, although it's more complex). The above calculation uses WINDOW_MAX to find the maximum values in the whole sheet. The only values that are ever thrown are those in the last 2 datapointts - the rest will be null, so the max will always pull only the 2 we want.

          • 2. Re: Line Color Based on Increasing or Decreasing trend
            Jim Dehner

            Hi

             

            I did this in 2 parts - first to determine the direction of the last segment of the line          

                      if LOOKUP(sum([Sales]),last()) - lookup(sum([Sales]),(last()-1))  <0 then 1 else 2 end

             

            Then to color the line in total

                      if [color line last segment]= 1 then "Red" elseif

                      [color line last segment]=2 then "Green" end

             

            Then place the color of the last segment on detail and the color total line on color and you will get this

             

             

            Or

             

             

            Let me know if this is what you expected

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful
            • 3. Re: Line Color Based on Increasing or Decreasing trend
              Abdul Subhan Mohammed

              Thank You so much Jim,

               

              I was able to get it working also with your approach but a single query is better than 2 and since we had one, I chose it as the correct answer. Yours is Correct as well !

               

              Thank You so much for responding. I really appreciate your helping me out here.

               

              Regards,

               

              Abdul

              • 4. Re: Line Color Based on Increasing or Decreasing trend
                Abdul Subhan Mohammed

                Thank You so much Ben !

                 

                It worked!

                 

                Abdul