3 Replies Latest reply on Dec 6, 2018 5:42 PM by swaroop.gantela

    Growth in N periods even though Date not in chart

    Eoghan Lyons

      Hi All,

       

      Having a spot of bother with this one. I wish to draw up a scatter plot chart. One one axis I want to show the average price of an item, on the other I want to show the growth in a different metric (called "Value") over N periods (I wish this to be flexible - but if that can't be flexible I won't lose any sleep).

       

      I attach a file with 1) the desired chart (almost) and 2) an example of the calculation I want to include in the scatter plot. The Scatter Plot chart will ideally have a different X axis, this is where I want to show the Growth in the "Value" measure over N periods. For the sake of ease let's use quarters or months if it's easier as a standard period length.

       

      If this has to be using a fixed rather than dynamic N change that's fine.

       

      Can this be done easily?

       

      Many thanks,

      Eoghan

        • 1. Re: Growth in N periods even though Date not in chart
          swaroop.gantela

          Eoghan,

           

          I'm not sure if I caught the gist, but maybe the below can give some ideas.

           

          I started with your growth table, and I dragged the Value Percent Difference pill

          over to the Measures area, which generated this calculated field:

          (ZN(SUM([Value])) - LOOKUP(ZN(SUM([Value])), -1)) / ABS(LOOKUP(ZN(SUM([Value])), -1))

           

          I created the N parameter, and replaced the -1s in the above:

          (ZN(SUM([Value])) - LOOKUP(ZN(SUM([Value])), -[Select N Months])) / ABS(LOOKUP(ZN(SUM([Value])), -[Select N Months]))

           

          This I placed on the Columns Shelf, AVG([Price]) on the Row's Shelf, and Month([Date])

          on the Detail shelf. The Growth compute using is set to month.

           

          Please see workbook v10.5 attached in the Forum Thread:

          Growth in N periods even though Date not in chart

          1 of 1 people found this helpful
          • 2. Re: Growth in N periods even though Date not in chart
            Eoghan Lyons

            Hi Swaroop,

             

            Many thanks for the suggestion. I've had a play around with it and I couldn't understand where my issue was, and then I realised.

             

            Long story short of the below. Is it possible for me to isolate the value at a certain point in time?

            Example:

            (New - Old)/Old

             

            - New is always the most recent date period.

            - Old is a manually selected date.

             

            What I'm looking for is a single point in time comparison to another single point in time.

             

            For example: I want to see the comparison to now versus X months/quarters ago. The worksheet you presented did show this, but it also showed the prior months versus X months before that particular month. I only want to show the comparison versus the latest date in the database.

             

            I have tried to arrange this myself but the problem is that let's say I'm looking at weekly data. If I want to see a week versus a year previously, I would need to include all the weeks in between those dates in order to get the calculation I need.

             

            Eoghan

            • 3. Re: Growth in N periods even though Date not in chart
              swaroop.gantela

              Eoghan,

               

              Apologies for being off track.

              Maybe the below will be closer to the goal.

               

              I think this should make it simpler now.

               

              The Most Recent Month Value will be an LOD that fixes it to every row:

              { FIXED :MAX(

              IF [Date (Months)]={MAX([Date (Months)])}

              THEN [Value]

              END)}

               

              Then you can filter down to the desired month and get the difference by

              ([Most Recent Month Value]-[Value])

              /

              [Value]

               

              As the values change from month to month, the automatic axes change too,

              so I made some reference bands to fix the graph to max and min differences and prices.

               

              Please see workbook attached in the Forum Thread:

              Growth in N periods even though Date not in chart