How do I superimpose a period over period in a visual without having the period that I'm comparing it to in the visual?

[e2s]

I’ve got the period over period working on the previous month but it only works if I’m visualising the previous month as well as the month I’m Comparing to. Is there any suggestions I could do to bring back the previous month into this month for comparison without having to bring back last month as well?

[Jesse]

Hello! Before I propose a solution, do you want this to be a bar chart in the end? Or are you trying to use a Table or some other visual type? Wasnt sure if the bar chart was just to illustrate the problem or if thats the chart type you want in the end (and just want to hide the first set of values from the initial period)?

[e2s]

Hi @Jesse , yeah we would like to hide the first set of values and use a Bar and Line chart (the lines will be representing another metric but won’t have a period over period associated to it. )

We also need to do the superimpose some data on a line chart month over month, YoY. Thanks

[Jesse]

We conceptually need to ‘hide’ those values and not filter them. To do this we will manipulate where this filter happens in the query pipeline, and by using a Table Calculation, we can make it happen all the way at the end, after the YoY calcs have already been computed.

Write a calc like this:

minOver(min({order date}), [{order date}])

And then put your filter on that. Should work!

Before:

After:

[e2s]

Brilliant! Thanks @Jesse for the explanation too! I have spent so much time trying to come up with a solution for this problem to no avail. Appreciate the solution

[Cindy]

Hi Jesse,

Found your solution is super helpful! Can you please advice How can I make it dynamic? ex: create paramater for user to choose the time period. Base on the paramater to only show the last month’s difference?

[Jesse]

Hi @Cindy - you can add a filter control from that same ‘Date Hider’ (or course you can call it something different) field. You can optionally choose ‘Relative Dates’ in the filter settings (rather than 'Date and Tie range, for instance) which will give users a nice UI to pick items like this month, last month, last X months, etc.

[Luca_Pecoroni]

Hi @Jesse
I just stumbled over your explanation here and found it super helpful, thanks!

But can you explain in more detail how you added the table calculation? Like do I need to add the code you shared as a calculated field or where do i add it?

Thanks!
Luca

[Jesse]

Hi @Luca_Pecoroni yes that formula I added using MinOver should be added as a new calculated field, then you add a filter (and optional control) to that new field. By Table Calculation I just mean that minOver, which is actually a Level Aware Calculation (LAC), can function as a table calc if you dont add PRE_AGG or PRE_FILTER to it, which makes it happen all the way at the end of the query/rendering pipeline.

[Luca_Pecoroni]

It worked, thanks a lot!

[Vaidy]

Hi @Jesse, Does the same solution work if I use a pivot table? In my pivot table I have a filter for year and If I select only 2023, I would like the YoY metric to show value for 2022.

[Jesse]

Hi @Vaidy yes should work for any visual type!

[msedlmair]

Hi @Jesse , thanks for the fix. It worked like charm last year but now in Jan 24’, i don’t have “last month spend” in my pivot table.

Fields:
last_month_unblended_spend. (which gives no values)
periodOverPeriodLastValue(sum({unblended_cost}),{Date (Period)},MONTH,1)

Adjusted Order Date:
addDateTime(dateDiff(maxOver({billing_period}, [], PRE_AGG), now()), 'DD', {billing_period})

Date (Period):

ifelse(
${PeriodStarting}='Day',truncDate("DD",{Adjusted Order Date}),
${PeriodStarting}='Month',truncDate("MM",{Adjusted Order Date}),
${PeriodStarting}='Quarter',truncDate("Q",{Adjusted Order Date}),
truncDate("YYYY",{Adjusted Order Date}))

Date Hider:

minOver(min({Date (Period)}), [{Date (Period)}], POST_AGG_FILTER)

I then filter via Date Hider for relative dates, “this month”.
It worked for “previous month” though.

[rusu2k]

Hi @Jesse,

I used this trick to hide rows in my pivot table and filter only visually.
It worked great, except 1 problem I have with Totals for some value fields.
This is how my table looks like:

The issue I have is that for the following fields:
$ increase vs. last year same month
% increase vs. last year same month
$ increase vs. last year (YTD comparisons)
% increase vs. last year (YTD comparisons)

It seems like the problem is only present on the Column Totals, while the columns grouped by category work as expected no matter what is selected in the Date Filter (defined as: minOver(min({Month of Adjusted Date}), [{Month of Adjusted Date}]))
However for totals, it only seems to work for the fields I mentioned when the months that are being compared to are also present in the table.

The formulas I am using for the fields are the following:
For Month:

sumOver(
sum({C Net Amount}),
[{Location Nickname}, {Month of Adjusted Date}, {CName Ordered1}]
)

$ increase vs. last year same month:

periodOverPeriodDifference(
sum({C Net Amount}),
{Month of Adjusted Date},
MONTH,
12
)

% increase vs. last year same month:

periodOverPeriodPercentDifference(
sum({C Net Amount}),
{Month of Adjusted Date},
MONTH,
12
)

Year to Date:

periodToDateSumOverTime(sum({C Net Amount}), {Month of Adjusted Date}, YEAR)

$ increase vs. last year (YTD comparisons):

periodOverPeriodDifference({C YTD$}, {Month of Adjusted Date}, YEAR, 1)

% increase vs. last year (YTD comparisons):

periodOverPeriodPercentDifference({C YTD$}, {Month of Adjusted Date}, YEAR, 1)

Previous Year:

{C YTD$} - periodOverPeriodDifference({C YTD$}, {Month of Adjusted Date}, YEAR, 1)

Thanks in advance!