Creating period comparisons with Quick Sight'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)

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

[racerrat]

Hi team, quick question. I have a Week parameter in a Dashboard, that is affecting several visuals. This week parameter shows the week number. In order to update it to the current week, I have to change it manually. Is there a way of making it update itself, so when the new week starts the parameter change, and therefore the visuals connected to it?

[Kristin]

@Jesse Can you reply to this question from @racerrat? Thx!

[Jesse]

Hi @racerrat - is your parameter a date field, or an integer (the week number)? If a date field you can set a rolling default to update it for instance to the ‘start of this week’ or ‘end of last week’.

If it is an integer parameter, there is a roundabout way to do this using Dynamic Default Parameters where you can have a separate dataset that updates with the new max week number. Rather than doing at a user level you should be able to do at the group level, but you would need a group in your quicksight account that has all users in it.

[racerrat]

Hi @Jesse. My parameter is an integer (just the week number). My dataset updates every week since it´s getting its data from an ETL. Is it possible to use the Dynamic Default Parameter in that way?

[Renato]

@Steph

Notice that the current period is showing a larger difference in previous and offset years than other periods.

Hi @Jesse . I see @Steph built a solution for this by making a KPI that show the PTD Value. But is it possible to merge both results in 1 column ? I was thinking about an ifelse comparing the date, if it’s the same as the actual period it shows the PTD value otherwise it shows the Offset year value, but I couldn’t figure it out.

Thanks in advance.