Performance issues in using multiple Values in Table visualization for YoY Calculation

[mtreiner]

Hey Quicksight-Community,

I have an analysis with a dataset containing 1000 rows (3 Dimensions: Time, Reporting Grain and Marketplace + 49 measures).
In one table, I want to display YoY Values using: periodOverPeriodDifference({measure}, {reporting_date}, MONTH, 12)

The calculations are materialized in the data prep stage (Based on Tips and tricks for high-performant dashboards in Amazon QuickSight | AWS Big Data Blog).

Whenever I add more than about 8 values to the table with the YoY calculation, Quicksight is saying that “Getting data for this visualization took too long”.

For the table, I am using following filters:

• Marketplace equals Worldwide
• Reporting Grain Equals Monthly
• A filter to select only the current month (Based on parameter selection) and the same month of last year
• A filter to display only the values for the current months year using (rank([min({reporting_date}) DESC])) (Based on Row context to calculate WoW, YoY, PoP - #3 by Andrea).

Is there a way in which I can improve the performance?
We are currently migrating the dashboard from Tableau to Quicksight and I have never faced such performance issues in Tableau for YoY Calculations.

Any help is appreciated.

Thanks in advance,
Marco

[Max]

Are you using a direct query or spice? SPICE will make it much faster and it shouldn’t be an issue.

Also, can you try and creating a new visual with this field? Sometimes the visual itself errors out for some reason.

[mtreiner]

I’m using SPICE.
I have tried to recreate the visual but now get the error message “Please contact the Quicksight team to solve this issue”

[gillepa]

Hi @mtreiner

can you please provide some screenshots?

What do you mean by ‘8 values’ in "Whenever I add more than about 8 values to the table with the YoY calculation, Quicksight is saying that “Getting data for this visualization took too long”? Columns?

Can you remove the filter to display the current month and see if you get the results you want? If you do, then we’ll then have to figure out how to hide the previous month.

[mtreiner]

Hi @gillepa

attaching you screenshots from the table in the message as well as from the “Field wells” section.
By 8 values I am referring to 8 different measures I am trying to display in the “Field wells” section.
I am still facing the same error when removing the filter for the current month unfortunately.

The first screenshot contains a table with 5 measures displaying recent YoY Values for November 2022.

When I am adding to this table more measures with the YoY calculation, the following error message is being displayed:

[gillepa]

Hi @mtreiner

The first screenshot is not enough to help me understand the context. Can you include more, visual type, rows, columns, field wells etc?

In the meantime, you can try maybe a trick: create multiple identical visuals, each with different PeriodOverPeriod measures, and arrange them on top of each other - only the visual on top would display the title - to ‘mimick’ one single table. It might actually help with the performance issue.

Try it and let me know,
GL

[Jesse]

The periodOverPeriod funcitons are Table Calculations, which means you need to include the date field in your visual (which is why you are also doing the rank filter to hide all the other periods). It also means these are not materialized in SPICE even though you put them in the dataset because they are aggregated fields.

You can calculate the PoP calcs another way which doesnt use table calculations and hence you wont need that Rank filter. I think this will perform faster.

For example, to do a YoY comparison at the month level (from this other article Period Over Period and Other Date Comparison Calculations (YoY, MoM, YTD, Rolling 90 days, etc)) :

(the ${AsOfDate} is a parameter in this example, but it could be another calc like maxOver({your date field}, [], PRE_AGG) to get the most recent date value)

This month (aka Month to Date, MTD):

ifelse(dateDiff({order_date},${AsOfDate},"MM") = 0, sales, 0)

Same month last year:

ifelse(dateDiff({order_date},${AsOfDate},"MM") = 12, sales, 0)

Then your YoY is:
(sum({Same month last year})-sum({This month})) / sum({Same month last year})

These wont be able to get materialized either because no matter if you use a parameter like AsOfDate or the maxOver calc to be your ‘end date’, both of those are going to prevent the calc from being materialized. It would have to be a hard-coded end date, or a real field from your data - like you could add a column in the actual data source that has the max reporting date listed on every row. Still, I even without being materialized you might have more success with this method. If still not working then I would try adding that max reporting date to the data source which will for sure make this faster.