8 Replies Latest reply on Jan 18, 2017 1:37 PM by Jim Dehner

    How to show % change over varying time periods

    Adam Powell

      I am trying to show % change of a counted value over time. My viz is a scatter plot with sales in the rows shelf and Quantity in the columns shelf. My date range is set by 2 parameters for start and end date with a filter to apply the parameters to the sheet.

      I have recreated my viz with the sample-superstore data source so I can attach an example.

       

      I started out with doing a YoY growth quick table calculation, and it worked out perfectly. I had the date range set to 1/1/15-12/31/16.

       

      Now, I would like for the table calculation to update when the date range is changed.

       

      How can I get the % difference in quantity over any selected time period? For example, if the date range is changed to 1/1/2016-12/31/2016, then the % difference would be 6 months over 6 months, not year over year. Or, if the date range is 11/1/2016-12/31/2016 then we will be calculating month over month. Essentially, we want to calculate the % change from the first half of the date range to the second half of the date range.

       

       

      Note: In the attached example I have 2 additional filters. The Top Customers by Profit filter is just to decrease the number of marks in the viz. The Quantity filter is there to show all non null values.

        • 1. Re: How to show % change over varying time periods
          Jim Dehner

          See the attached solution

           

          First calculate the number of days at the mid point between start and finish dates

           

          Int(DATEDIFF('day',[Start Date],[End Date])/2)

           

          Then convert that to a date

          DATEADD('day',[days diff],[Start Date])

           

          Then determine the sales in the first half (Numerator)

          if([Order Date] >= [Start Date] AND [Order Date] <= [date at mid point]) Then [Sales] else 0 end

           

          and second half sales (denominator)

           

          if([Order Date] >= [date at mid point] AND [Order Date] <=[End Date]) Then [Sales] else 0 end

           

          and then the % growth

          (sum([Numerator])/sum([Denominator]))

           

          then you can use the value as you want

           

           

           

          let me know if this works

           

          Jim

          • 2. Re: How to show % change over varying time periods
            Jamieson Christian

            Adam,

             

            My solution differs a bit from Jim's, because I observed that you want to be able to do month-over-month comparisons. In that case, splitting the reporting periods at the day level may not give you quite what you want. My solution assumes that your granularity will be no lower than the month, but then the rest of the calculations are essentially the same as how Jim approached it.

             

            The core of my solution is the calculated field [Reporting Period], which contains 1 or 2 to indicate if the sale falls in the first half or second half of the parameter-based reporting range. (And 0 if it falls outside of the range, so we can filter those out conveniently.) Note: this field also obviates the need for your [Date Range] boolean calculation.

             

            [Reporting Period]

            IF [Order Date] < [Start Date] OR [Order Date] > [End Date]
                THEN 0
            ELSEIF [Order Date] < DATEADD('month', INT((DATEDIFF('month', [Start Date], [End Date])+1)/2), [Start Date])
                THEN 1
                ELSE 2
            END

             

            I used a table calc filter on [Reporting Period] to show only "2", which is the second half of the reporting period, while retaining all of the underlying data that is needed to compare it to the previous reporting period "1".

             

            See my attached workbook. The second tab is my solution, and you can change the parameters to see how it reacts.

            2 of 2 people found this helpful
            • 3. Re: How to show % change over varying time periods
              Jim Dehner

              Nice solution

              I wanted to give the max flexibility by going to days -

              Jim

              • 4. Re: How to show % change over varying time periods
                Jamieson Christian

                Jim,

                 

                Agreed. There are caveats to both approaches.

                 

                • My approach will set an inappropriate midpoint if the selected period spans an odd number of months.
                • Your approach will set an inappropriate midpoint if comparing e.g. March (31 days) to February (28 days).

                 

                It's good for Adam to see both approaches and the pros/cons of each!

                • 5. Re: How to show % change over varying time periods
                  Adam Powell

                  Jim,

                   

                  Looks like growth % is only returning positive values. I would like to show when there is negative growth as well, but this is getting me very close! Thank you.

                  • 6. Re: How to show % change over varying time periods
                    Adam Powell

                    Jamieson,

                     

                    This looks great. The % change is updating properly when I update the parameters. This is what I was looking for. Much appreciated.

                     

                    You were correct in noting that it will not work if comparing an odd number of months. I will dive in and try to find a solution to that so, that when looking at a Quarter, it will work.

                    • 7. Re: How to show % change over varying time periods
                      Jamieson Christian

                      Adam,

                       

                      If you're trying to compare quarter-over-quarter, my solution will work fine, because the total # of months will be 6 (and the midpoint will be correctly placed 3 months in).

                       

                      If you try to compare first half of a quarter with the second half of the quarter, then my solution will go wonky, because the total # of months will be 3.

                       

                      You can certainly augment the formula to behave differently if it detects different types of time ranges in the parameters. It will get pretty messy looking, but totally doable.

                      • 8. Re: How to show % change over varying time periods
                        Jim Dehner

                        right you are - corrected the formula and it should work now

                        Jim