Table with multiple Period-To-Date and Period-Over-Period metrics

Question & Answer
, ,
1

Hi everyone :wave:t2:

Once again I would need your expertise.

What I want to achieve: a table showing multiple Date-ToPeriod and Period-Over-Period metrics. Something like this:

Metric Today DoD Week-To-Date WoW Month-To-Date DoD Quarter-To-Date QoQ Year-To-Date YoY
Revenues €100 -50% €600 +10% €1,300 -5% €4,500 +2% €15,000 +10%
Deposits €200 -30% €800 +12% €1,200 -5% €5,200 +12% €12,000 +15%
Registrations 10 +4% 60 +3% 150 +2% 500 -5% 800 -2%
Conversions 5% +1% 4% +1% 8% -1% 12% +8% 11% +7%

I started working with PeriodToDate and PeriodOverPeriod functions, but it seems like it is not possible to achieve what I am looking for: first of all I would’ve to create 10 calculated fields for each metric, which means around one hundred of calculated fields in total just for this particular table, and second I do not think there is a way that I can create a table similar to the above. In my experiments I’d get one row for each calculated field.

I modeled my data so that it’d be something like this:

User Created Registration Product Deposits Amount Orders Amount Orders Count
001 2022-12-23 00:01 1 NULL NULL NULL NULL
002 2022-12-24 01:18 1 NULL NULL NULL NULL
001 2022-12-24 10:00 NULL NULL €100 NULL NULL
001 2022-12-24 11:00 NULL A NULL €50 1
001 2022-12-24 12:00 NULL B NULL €150 3

There are more fields but it gives an idea.
I used some SQL to have one column for each transaction type instead of having them in rows based on a type ID.
Some of my metrics are calculated fields,e.g.: conversion rate, while most are simple sums.
Is there any way to use the functions above to aggregate the data as above?
I am pretty sure that there is, I cannot believe that I would really need to write down one hundred functions for it just replacing one or two fields in each, but the issue with QuickSight is that there is not a huge body of knowledge out there, and even with extensive Google, I cannot find anything really pertinent (the way the period functions is straightforward but it doesn’t help me much here, I believe).

Thanks everyone :bowing_man:t2:

2

Hi, Please check this post in the community may help in meeting your requirements - Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 7 days, etc)

Thank you
Deepak

3

Hi @DeepakS,
Thank you!

I actually went another way, creating several custom fields:

Selected_Week: periodToDateSum({Amount}, {Created_On}, WEEK)

Selected_Month: periodToDateSum({Amount}, {Created_On}, MONTH)

Previous_Week: sumIf({Amount}, {Created_On} <= addDateTime(-1, 'WK', addDateTime(1, 'DD', ${DateSelector})) AND {Created_On} >= addDateTime(-(6 + extract('WD', ${DateSelector})), 'DD', ${DateSelector}))

Calculating the PoP simply as (selected_period - previous_period) / previous_period etc.
Not sure that this method is better than mine, seems like I’d have to write a lot of custom calculations as well since they refer to different metrics, and this is what I find frustrating the most (as per my example above, I would have registrations which is based on count of users, and then financial amounts, which are based on other fields.

Thanks a lot, I will be looking at that solution to understand if it can make the process simpler :slight_smile: