Period Over Period Calculations

Question & Answer
, , ,
1

Hello everyone! I could not figure out period over period calculations since I am pretty new.

Here is my dataset:

image
image1382×360 21.1 KB

Basically value equals to numerator/denominator for percentage metrics but for decimals it directly passes. So it gets complicated for me to create a WoW column for both percentage and decimal metrics.

For date part I have created final_report_date column from SQL by getting first date of the week. So my dates are only showing the first day of the week.

For month part I have created something like this , same applies for numerator and I have to create the same for one month before and it takes to much time. Also I could not extract weeks.
SUMIF(denominator, {report_months} =extract(‘MM’,adddatetime(-1,‘MM’,NOW())) and {report_years}=extract(‘YYYY’,adddatetime(-1,‘MM’,NOW())) )

Test11

And here is the final view that I want to create:

Lastview
Lastview1402×331 51 KB

2

Hello @Ozzy, I totally understand how the period over period calculations can be difficult, so I will do my best to help. Also, thank you for taking the time to put the data into an analysis in Arena, that makes it a lot easier for me to help you.

The first thing that I noticed is that your final_report_date is showing as a string field and not a datetime field. I’m not sure if that is the same in your actual analysis, but if it is, it will complicate some of these calculations. Another thing that I want to mention is that showing some things like WoW, MoM, etc. it a pivot table do not always work how you want. If you were displaying your data in weeks, you could show a WoW column with each week, but trying to show 1 WoW column at the end likely will not work.

Now, some things that might be worth playing around with to get your periodOverPeriod values would be sumOver and Lag functions.

sumOver is a great way to get a value by a partition and it should also allow you to compare your value for a specific week to the previous week value returned from Lag.

lastMonthValue = lag({report_metric}, [truncDate('MM', {final_report_date}) DESC], 1)

This should return your previous month value on a row containing the current month value. Then you could subtract it or divide it from the current month value that you calculate with sumOver.

thisMonthValue = sumOver({report_metric}, [truncDate('MM', {final_report_time})])

Those functions should allow you to aggregate them together. Then if you want to compare week values just change the truncDate function in the partition fields to truncDate('WK', {final_report_date}).

Let me know if you have any follow-up questions or if you run into any issues implementing these functions and I can try to guide you further. Thank you!

3

Hello @Ozzy, since we have not heard back from you, I wanted to check in and see if my response helped you receive your expected output? I will mark my last reply as the solution for now, but if you have any further questions or run into any errors, please let me know!

4

Thanks for all the help Dylan , here is what I did. Since I had to prepare metrics according to its type I have defined my metrics under metrics_type calculated field than I have prepared these for every time period that I have.

cur_q_denominator

sumIf(denominator, truncDate(‘Q’, {final_report_date}) = truncDate(‘Q’, addDateTime(0,‘Q’,now())))

cur_q_numerator
sumIf(numerator, truncDate(‘Q’, {final_report_date}) = truncDate(‘Q’, addDateTime(0,‘Q’,now())))

cur_q_num/den
ifelse(
sum(ifelse({metrics_type}=‘percentage’ or {metrics_type}=‘ratio’,0,1))=0, {cur_q_numerator}/{cur_q_denominator},
sum(ifelse({metrics_type}=‘currency’ or {metrics_type}=‘decimal’ ,0,1))=0, {cur_q_numerator},
null)

and -1 for quarter before. Finally for QoQ I have created this :
ifelse(
sum(ifelse({metrics_type}=‘percentage’,0,1))=0,
ifelse( isnull({cur_q_num/den}-{lq_num/den}),‘’,concat(‘’,substring(toString(round(({cur_q_num/den}-{lq_num/den})*10000,1)),0,locate(toString(round(({cur_q_num/den}-{lq_num/den})*10000,1)),‘.’)+0),’ bps’))

,sum(ifelse({metrics_type}=‘ratio’ or {metrics_type}=‘currency’ or {metrics_type}=‘decimal’ ,0,1))=0,

ifelse( isnull({cur_q_num/den}/{lq_num/den}),‘’, concat(‘’,substring(toString(round(({cur_q_num/den}/{lq_num/den}-1)*100,2)),0,locate(toString(round(({cur_q_num/den}/{lq_num/den}-1)*100,2)),‘.’)+3),’ %')),null)

Do you think this would be the right approach? If I can improve this in a shorter way that would be better for me :slight_smile:

1 Like
5

Hello @Ozzy, if those functions are giving you the expected values then I think you are good to go! There may be an alternative solution using the sumOver functions instead, but honestly I don’t know if it would really be much shorter than the way you have currently implemented. I’d say stick with your current calculations!