Filter all the combinations of two fields that for a specific aggregation have a certain value

[Fquiroga95]

Basically I want to create a filter that allow me to filter on all the combinations of Country and Categories that have a YoY difference based on last full month, greater or less than 100%.

Ex: Brasil-Food & Beverages has 3M in Sales in January 2024, compared to 2.7 January 2023. Having a YoY of 11,11%.

So the filter which will be used in a filter pane, should allow me to select all the possible combinations below and above 100% of YoY.

In this specific case, I can't interact with the script, is from a Business Analyst perspective. 

This is the expression I am using, but it is filtering wrong results. I end up with values below and above 100%

=AGGR(
if(
(
Sum({<MonthStart={'$(=MonthStart(Addmonths(vMaxDate,-1)))'},Country=,Category=>} Revenue)
/ Sum({<MonthStart={'$(=MonthStart(Addyears(Addmonths(vMaxDate,-1),-1)))'},Country=,Category=>} Revenue)
- 1
) >= 1,
'100% and More',
'Less than 100%'
),

Country,Category)
)

Would appreciate some help,

Best regards.

[Digvijay_Singh]

You would need to share more info like, chart type you are using, some sample data and how expected output should look like.

What happens when you execute individual expression 'Sum({<MonthStart={'$(=MonthStart(Addmonths(vMaxDate,-1)))'},Country=,Category=>} Revenue)' separately in a KPI object?

Do you see your MonthStart field showing right value in the expression editor? I suggest check your data filter conditon is resulting into right value or not?

More info will help community members to try few things around your data to help you further.

[Fquiroga95]

Hello and thanks for your reply.
It is for a filter pane, it should filter Country field and Categories field. Then there are some charts showing sales, yoy, etc. but the idea is this expression will be used for the filter resulting in two values, less and more than 100%.

Yes, I can see MonthStart is showing the right value in the expression editor. 

Even, if I put in a table a dimension like Country&' '&Category, YoY Expression in another column, and this, without the aggr:

if(
(
Sum({<MonthStart={'$(=MonthStart(Addmonths(vMaxDate,-1)))'},Country=,Category=>} Revenue)
/ Sum({<MonthStart={'$(=MonthStart(Addyears(Addmonths(vMaxDate,-1),-1)))'},Country=,Category=>} Revenue)
- 1
) >= 1,
'100% and More',
'Less than 100%'
)

it correctly shows each row with the YoY type, less or more than 100%.

[Fquiroga95]

The problem I see is that is filtering on all the countries and categories when selecting less than 100%, what I think is happening is that all the combination of country and categories that have less than 100%, include all the countries and categories, but it is not assessing the specific combination of Country and Category.

Ex Italy - Clothing has 10%

     France - Ice Cream 70%

     France - Clothing 500%

     Italy - Ice Cream 1000%

Only two combinations have less than 100%. But individually speaking about the fields, it will select both Italy and France Countries, Ice Cream and Clothing Categories.. Unfortunately probably is not possible to filter on this without creating a concatenated field in the backend with Country and Category