Period Over Period Functions with Date Controls

[zwaters]

Hi all,

Anyone know if it’s possible to Period Over Period functions with a date control? My leadership team would like to see YOY data shown as YTD, but also wants the ability to change the dates so they can view Mar - May for example.

Every Period Over Period function I’ve tried to use does not allow me to filter different dates. I can properly calculate YOY, but as soon as I select a date then the YOY disappears or shows ‘No Data’.

In Power BI, the way I got around this was to use an ‘ALL()’ function, which essentially allows you to ignore all selected filters while performing the calculation. I have been trying to find something similar in Quicksight but haven’t had any luck.

Any assistance would be greatly appreciated.

[royyung]

Can see if this “DateHider” is what you are looking for?

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

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: [image] After: [image]

[Jesse]

Hi Zenia and Roy - I think that post above will help if you are building a line chart or bar chart where you want to show YoY of many data points at the same time. If you just want a single KPI but allow the user to filter to different date ranges, I would suggest using Parameter controls rather than Filter controls and then building all the YoY logic into your calculated field. Imagine you have two parameter date controls - start and end. Then your calc would look like this:

ifelse({order date}>=${start} and {order date}<=${end}, sales, 0) - ifelse({order date}>=addDateTime(-1, 'YYYY',${start}) and {order date}<=addDateTime(-1, 'YYYY',${end}), sales, 0)

You could break it down into 3 calcs instead (which would allow you to use the Current Period and Same Period Last Year values by themselves if you wanted, and then your 3rd calc just does the arithmetic using those to do either YoY or YoY %.

Current Period: ifelse({order date}>=${start} and {order date}<=${end}, sales, 0)

Same Period Last Year: ifelse({order date}>=addDateTime(-1, 'YYYY',${start}) and {order date}<=addDateTime(-1, 'YYYY',${end}), sales, 0)

YoY: sum({Current Period}) - sum(Same Period Last Year})

YoY %: sum({Current Period}) / sum(Same Period Last Year}) -1

Check out this working example: https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-YoY-using-parameters

[zwaters]

Hi Jesse,

This is amazing - exactly what I was looking for! Great idea to use parameters instead of date controls. I am new to QuickSight, so trying to learn more about parameters and how they can be used.

I really appreciate your response, we have been stumped on this for quite a while.

Thank you so much!
Zenia

[zwaters]

Thank you for sharing that link, Roy! I did try to use the “DateHider” solution, but unfortunately it seems to only work with a bar or line graph, and I was looking to use a KPI visual instead. This will definitely come in handy as I will be looking to graph the YOY values at some point.

I appreciate your response on this one!

[Anya]

Hey! i have a similar thing to calculate, i need to calculate YoY growth in pipeline and have it as a scatter plot against the YTD pipeline. How can i modify this to achieve that?

[Jesse]

Hi @Anya - Can you help me picture what you are trying to create? What would each point be on the scatterplot, a month, a product? And you would have an axis for YoY % and the other is YTD pipeline (as a running sum)?

[Anya]

I am trying to create a scatter plot with the following axis:
x-axis: yoy% growth in pipeline for each customer (between years 2022 and 2023)
y axis: YTD Pipeline of each customer

I want to create the scatter plot in the form of a quadrant chart with the following quadrants:

1. High pipeline/low growth
2. Low pipeline/ low growth
3. High pipeline/ high growth
4. Low pipeline/ high growth

each point on the quadrant chart will represent a customer.

[Jesse]

Hi @Anya You should be able to create that using the example formulas listed in this article. Look at ‘This year’ and ‘Last year to date’. Use those to write your YoY% calc as (sum(YTD)-sum(PYTD))/sum(PYTD)

Was able to get a scatterplot which I think is similar to what you are after.

[Jesse]

@Anya I figured out the quartiles calcs to color them by quartile (there are no reference lines on scatter plots yet, so this is best we can do at the moment).

YTD SumOver:
sumOver(YTD, [{customer id}], PRE_AGG)

PYTD SumOver:
sumOver(PYTD, [{customer id}], PRE_AGG)

Quartiles:

ifelse(
    percentileRank([{YTD SumOver} ASC],[], PRE_AGG)>50
    AND 
    percentileRank([({YTD SumOver}-{PYTD SumOver})/{PYTD SumOver} ASC],[], PRE_AGG)>50,'High pipeline/ high growth'
    ,
    percentileRank([{YTD SumOver} ASC],[], PRE_AGG)<=50
    AND 
    percentileRank([({YTD SumOver}-{PYTD SumOver})/{PYTD SumOver} ASC],[], PRE_AGG)>50,'Low pipeline/ high growth'
    ,
    percentileRank([{YTD SumOver} ASC],[], PRE_AGG)<=50
    AND 
    percentileRank([({YTD SumOver}-{PYTD SumOver})/{PYTD SumOver} ASC],[], PRE_AGG)<=50,'Low pipeline/ low growth'
    ,
    'High pipeline/ low growth' 
)

End result:

[Harsheena]

Hi @Jesse ,

I have a similar issue, could you kindly help me with that?

I found my daily production value using the period-over-period difference, and that is accurate I applied the minOver(min(TMST}), [{TMST}]) to my visualization through the filter, which is also working fine, Now I would like to find the average of these daily production value, how can I find it? I tried to find the running sum of my calculated which also gave me inaccurate results.

[Jesse]

Hi @Harsheena I think you want to use the windowAvg function. Something like windowAvg(sum(daily_production), )

[Harsheena]

@Jesse , Thanks for your reply.

When I apply the above formula, I get this error “At least one of the arguments in this function is an aggregate function nested within another aggregate. Nesting of aggregate function is not allowed.”

[Jesse]

@Harsheena sounds like maybe your daily_aggregate is a calculated field that is already aggregated. Try removing the sum() from the new calc. windowAvg(daily_production, )

[Harsheena]

@Jesse, When I try the formula this way windowAvg({Daily_Production}, []) I get this error, this function does not have the correct number of arguments.

Actually, I wanted to find two values, one is the sum of the Daily Production and the average of daily production but I get errors and am not able to find these values.

Any help would be really appreciated