YTD Comparisons

[cameron.bumstead]

I am trying to build out a table that will show me a YTD pipeline generated comparison to the previous year. For example, I’ll have Jan, Feb, Mar, Apr, etc as the columns and then the value would be YTD pipeline generated through April (for example) compared to the YTD pipeline generated through April of the prior year.

I have tried the formula below to compare the YTD number through each month compared to the prior year, but it only will compare the monthly sum of the given month to the monthly sum from the prior year, as opposed to adding in the months from earlier in the year.

periodOverPeriodPercentDifference(sum({SDR Pipeline Generated}),{discovery_date__c},YEAR,1)

[ErikG]

Hi @cameron.bumstead
Maybe this will help.

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…

BR

[cameron.bumstead]

Hi Erik!

I feel like this is getting me closer, but not quite what I’m looking for. It still doesn’t allow me to compare YTD with the to-date part being in the past. I want to show YTD through each individual month (column) and compare that to YTD through the prior year of the same month.

[ErikG]

What about get the last value of the prior year on the current month, first.

Maybe you can share a few sample data or an Arena analysis?

[Neeraj]

Hi @cameron.bumstead
In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here.(Details on using Arena can be found here - QuickSight Arena )
We will take a look once we have the sample from you.

[cameron.bumstead]

Pipeline Generation

I’m new to QuickSight and this Arena feature is really cool! Anyway, I would like to compare the 302 YTD in Jun 2023 to the 246 YTD in Jun 2022 and put it under the 92%, which is the month to month comparison. And then I’d like to do that for every month so we can see pacing differences over time.

[Neeraj]

Hi @cameron.bumstead ,
Thanks for sharing analysis . when you say you need prior YTD i.e June 2022 value under 92% . are you looking to achieve this outcome ?

[sagmukhe]

Thank you @cameron.bumstead for sharing the details. @Neeraj and @ErikG have pretty much shared the relevant details with you. If you are looking for exact calculation then you should probably try something like this :

1. Create a Calculated field for the Prior year values. In my example I used :
   periodOverPeriodLastValue(sum(Sales),{Order Date},MONTH,12)
2. Project that field in the table. Now you should be able to see that the Sales information of the current year and prior year are coming side by side against a given month.
3. Transpose the table to get the required view that you have shared. Hope this helps!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

[ErikG]

Pipeline Generation

Works as long as you dont set a filter.

[cameron.bumstead]

THANK YOU!!! Turns out I was really close, I was just hitting issues where it was telling me I can’t nest aggregated functions or something along those lines.