Creating period comparisons with QuickSight's periodOver and periodTo functions

[Anubhav_Gupta]

Hi @Jesse Thanks for looking into this. Yes, my calculations are same but when I do week comparison it only shows me week start date in x-axis. Is it beacuse of Date (Period) calculated field whose calculation is as below:

ifelse(
${Periodold}='Minute',truncDate("MI",{Adjusted Date}),
${Periodold}='Day',truncDate("DD",{Adjusted Date}),
${Periodold}='Week',truncDate("WK",{Adjusted Date}),
${Periodold}='Month',truncDate("MM",{Adjusted Date}),
${Periodold}='Quarter',truncDate("Q",{Adjusted Date}),
truncDate("YYYY",{Adjusted Date}))

for adjusted date:

addDateTime(dateDiff(maxOver({date_time}, [], PRE_AGG), now()), 'DD', {date_time})

I used this as a reference from the above tutorial.

[P_G]

Hi Jesse. What should I do if my Sales is a pre aggregated function? For example imagine that here I would have sum sale and the function wouldn’t work - what can I use instead to make the below work?:
ifelse(${PeriodStarting}=‘Day’,periodToDateSum(sum(Sales), {Adjusted Order Date}, ‘DAY’, now()),
${PeriodStarting}=‘Month’,periodToDateSum((sum(Sales(, {Adjusted Order Date}, ‘MONTH’, now()),
${PeriodStarting}=‘Quarter’,periodToDateSum((sum(Sales, {Adjusted Order Date}, ‘QUARTER’, now()),
periodToDateSum((sum(Sales(, {Adjusted Order Date}, ‘YEAR’, now()))

[ginevrap]

Hi @Steph, thank you so much for your detailed explanation. Probably one of the best I’ve found so far! I have a follow up question on creating the KPI visual for another calculated field I have. I am replicating the same formula for current year PTD and previous year PTD but I am getting an error message.

Current year PTD

ifelse(${PeriodStarting}=‘Day’,periodToDateCount({Qualified POs}, {Adjusted Submission Date}, ‘DAY’, now()),

${PeriodStarting}=‘Month’,periodToDateCount({Qualified POs}, {Adjusted Submission Date}, ‘MONTH’, now()),

${PeriodStarting}=‘Quarter’,periodToDateCount({Qualified POs}, {Adjusted Submission Date}, ‘QUARTER’, now()),

periodToDateCount({Qualified POs}, {Adjusted Submission Date}, ‘YEAR’, now()))

Qualified POs is a calculated field based on this formula below which says to count all opportunities with ‘Approved’ status:
countIf({apn_opp_status__c},{apn_opp_status__c}=‘Approved’)

When I try to save the current year PTD formula, I get an error message basically saying I cannot nest aggregated formulas. Is there a workaround for this? My goal is to see PTD values of current year vs previous year for th e# of opportunities approved.

Thank you!
Ginevra

[KalaiDot]

@Steph

for a specific granularity

Hi,
Thanks in Advance. if iam using with categorty in stacked bar chart for peroid over period comparison iam getting error. Can you please help how to create comparison using bar stacked chart for current year and pervious year. Iam using formula for previous year ,
periodOverPeriodLastValue(sumover(sum({LIFE_MOVE}),[{Date (Period)},REGION]), {Date (Period)}, YEAR, 1)

[KalaiDot]

Hi @Steph ,
This post was Very Helpful. When i was insert on leap year date(feb292024) Entire data was missing around this month for previous year. but i can see in normal date field grouping.
Attached screen shot for ref.

[Kristin]

@Jesse Could you view and field the above from Steph’s perdiod comparisons article for @KalaiDot ?

[KalaiDot]

Hi @Steph ,
How to add periodtodatesum function in table visual using group by Date(period). if iam using Previous Year PTD in table visual it shows only for current year.

[Jesse]

Hi @KalaiDot you might consider trying to pre-calculate YTD and PYTD as two separate numerical fields using this technique instead.

Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)

Often you may want to create tables and other visuals which display multiple fields that are all ‘filtered’ to different date periods, as well as period-over-period fields. However we can’t use normal filters for this since filters get applied to all fields in the visual. Conceptually in this solution we are building the filters directly into the calculated field itself (using the ifelse logic), so that each field can have a different ‘filtered’ time period. You can then do whatever math you wan…

[Jesse]

Hi @KalaiDot - maybe it would help to understand what you want your final visual to look like, it will affect how we choose to write the calc. In your first screenshot above you have Jan 1 202X as rows, is that what you want to have (1 row per year)? If so then then both number columns should show the YTS and PYTD values? Then the 2024 LYTD value would be the same as the 2023 YTD value (just pointing out to make sure we are on the same page).