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

# How to show % change over varying time periods

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

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

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

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 0ELSEIF [Order Date] < DATEADD('month', INT((DATEDIFF('month', [Start Date], [End Date])+1)/2), [Start Date])    THEN 1    ELSE 2END`

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

Nice solution

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

Jim

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

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

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

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

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

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

Jim