periodOverPeriodPercentDifference with negative and positive numbers

In Excel, if I wanted to compare against two numbers where it could be a combo of either positive or negative from the old vs new values, I just take do =(A2-A1)/ABS(A1)

which gives me either a positive percent change being an improvement, and a negative percent change being a degradation.

In QS, does the function periodOverPeriodPercentDifference do something similar?

Hello @Clayton_Barton, yes the periodOverPeriodPercentDifference calculation will do a similar functionality. Based on the period type and your date field, it will determine the percent difference (positive or negative) in that time frame. I just posted a solution to another topic related to this that might be worth looking at, I’ll link that here. Also, I will include a link to the aws documentation for the function here.

Let me know if that helps!

The periodoverperiodpercentdifference computation calculates correctly, but what I’m looking for is the percentage itself to be positive for good and negative for bad.

So, for example, I have two negative numbers
Week 1 Profit is -125,014
Week 2 Profit is -132,469

The answer comes out as a positive 6%; but I want it to be a -6% because we got 6% worse.

In Excel, I can just do (-132,469 - -125,014) / ABS (-125,014) to get a -6%.

Hello @Clayton_Barton, I apologize for my delayed response. The one issue I am seeing with creating a solution for your question above is that the Sales for the week 1 will exist on a different row than the periodOverPeriod value that you would want to aggregate it with.

My inital thought would be to try and add the Previous Week Profit as a new column in your dataset so that you could easily compare the current week and previous week on the same row in your dataset.

Then you can write a pretty simple function in a calculated field like the one you mentioned in Excel above ({Previous Week} - {Current Week})/{Previous Week} and you should get the answer you are looking for.


I created a custom formula to apply absolute value .to a percent change calc. Thought it might help before a feature request is implemented.

    /* current day */
    windowSum({net_tasks}, [{reporting_date} ASC], 0, 0) -
    /* wondow must include curent day bc start and end must be positive integers */
    /* subtract two windows to get value outside of current day */
    (windowSum({net_tasks}, [{reporting_date} ASC], 1, 0) - windowSum({net_tasks}, [{reporting_date} ASC], 0, 0))
/* absolute value to handle negative values (of same subtracted formula above) */
abs(windowSum({net_tasks}, [{reporting_date} ASC], 1, 0) - windowSum({net_tasks}, [{reporting_date} ASC], 0, 0))