periodOverPeriodPercentDifference with negative and positive numbers

Question & Answer
,
1

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?

2

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!

3

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%.

4

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.

5

Hi,

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))