periodOverPeriodDifference and bottom movers year to year

[posac]

Hi,

Im new with quicksight - so sorry If I wrote something stupid

Dataset: I have orders database for us most interesting columns will be :

• customer name
• order weight
• date

Task Im struggling is to prepare visual that will show 10 customers with worst year to year comparison on order weight.

My approach :

• I have parameter that claims what type of date aggregation im interested
• I have calculated field that converts date to week, month, or quater depending on paratere
• I have used periodOverPeriodDifference with sum over calculated field defined above with 1 year offset - and its displayed correctly: periodOverPeriodDifference(sum({weight}), {Date Loc Period}, YEAR, 1)

So everything looks nice until I don’t define filter where I pick “top and bottom” with field with periodOverPeriodDifference…

then Im getting

Any suggestions ? whats could be wrong?

[abacon]

Hi @posac,

Welcome to the QuickSight Community and thanks for posting your question.

The order of evaluation in QuickSight means that the Top/Bottom filter will be executed before the Table Calculation periodOverPeriodDifference.
Are you able to pre-compute the period over period differences before bringing the data into the QuickSight dataset? If so, then you can apply the Top/Bottom N filter.

Many Thanks,
Andrew

[posac]

Hi @abacon

Thx for your reply !

I will be not able to calculate it upfront

our order model contains more data ofc and it can be filtered on several different ways , also we had requirement to be able to compare on different date aggregation, like on week/month/quater

[Xclipse]

Hi @posac, hope these links 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…

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!