Top N filter regardless the sign (absolute)

Q&A
, , , , ,
1

Hello,

I have a Stacked Bar Chart with month over month difference based on a Calculated Field.

MoM_sales_diff = “periodOverPeriodDifference(sum(Sales),{Order Date}, MONTH, 1)”

In my visual I have Order Date as X Axis, Item_type in Group By, and MoM_sales_diff as value.
The Stacked Bar Chart has positive and negative numbers (eg, 100, 90, 40, 35, 20, -5, -10, -70, -80, -90)
I need to filter for Top 4 variation, and get 100, 90, -90, -80.

I thought to create another calculated field as MoM_sales_diff and apply the absolute function, but in the filter I can’t use a field not part of my Visual Fields in the Top and Bottom filter.

Any suggestion on how to implement this?

2

Hi @awsgv ,

have you tried to see if Ranking can be a solution?

If you need the top N with respect to the whole variations, you can try creating a calculated field like MyRank:

denseRank(
    [abs(periodOverPeriodDifference(sum(Sales),{Order Date}, MONTH, 1)) DESC],
    []
)

At this point you just filter by MyRank less than 4 and that should work (if I understood correctly your needs).

Let me know if this helps!

Andrea

1 Like
3

Grazie Andrea,
It works great.
I added a date filter as well (billing_period - Last N month, 3, excluding 1) and I get exactly what I was after.
Much appreciated.

1 Like