periodOverPeriodDifference and bottom movers year to year

Q&A
, ,
1

Hi,

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

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
image

Any suggestions ? whats could be wrong?

2

Hi @posac,

Welcome to the Quick Sight Community and thanks for posting your question.

The order of evaluation in Quick Sight 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 Quick Sight dataset? If so, then you can apply the Top/Bottom N filter.

Many Thanks,
Andrew

3

Hi @abacon

Thx for your reply ! :slight_smile:

I will be not able to calculate it upfront :frowning:

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

4

Hi @posac, hope these links help:

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 Quick Sight Community!