topic Expression that compares difference of two sets based on filtering with aggregation in App Development

Expression that compares difference of two sets based on filtering with aggregation

NattiNatey — Tue, 24 Jun 2025 20:29:43 GMT

I am using the following expression to filter for count of a dimension based on filtering with aggregation with an alternate state filter:

{[Group 1]} COUNT(
IF((AGGR(SUM([Sales), [Product]) >= 50)

AND (AGGR(SUM({<Category={'E'}>}[Sales]), [Product]) >= 20), [Product])
)

My goal is to use an alternate state to create an identical expression with another alternate state like below:

{[Group 2]} COUNT(
IF((AGGR(SUM([Sales), [Product]) >= 50)

AND (AGGR(SUM({<Category={'E'}>}[Sales]), [Product]) >= 20), [Product])
)

Then I want to compare the dimension values in each set to determine which values exist in Group 1 that do not exist in Group 2. The alternate state filters will be just a simple dimension = value (i.e. Year = 2025, Year = 2024)

The result of the expression will be the count of unique values only in Group 1.

I tried below but had no success.

{[Group 1]} COUNT(
IF((AGGR(SUM([Sales), [Product]) >= 50)

AND (AGGR(SUM({<Category={'E'}>}[Sales]), [Product]) >= 20), [Product])
)

)>
-
<

{[Group 2]} COUNT(
IF((AGGR(SUM([Sales), [Product]) >= 50)

AND (AGGR(SUM({<Category={'E'}>}[Sales]), [Product]) >= 20), [Product])
)

)>}

Any suggestions appreciated!

Re: Expression that compares difference of two sets based on filtering with aggregation

Lisa_P — Wed, 25 Jun 2025 00:24:36 GMT

Your first expression should be:

{Group 1}Count({<Product={"=Sum(Sales)>50"}>*<Category={E}>}Product)

and the second should be:

{Group 2}Count({<Product={"=Sum(Sales)>50"}>*<Category={E}>}Product)

so next would be ..

{Group 1}Count({<Product={"=Sum(Sales)>50"}>*<Category={E}>}Product)

{Group 2}Count({<Product={"=Sum(Sales)>50"}>*<Category={E}>}Product)

Re: Expression that compares difference of two sets based on filtering with aggregation

NattiNatey — Wed, 25 Jun 2025 21:17:32 GMT

Hi @Lisa_P , thanks so much for your response! I was unaware of this syntax where you can use the intersection of two filtered sets of data. I think there is one difference in your solution than in my question.

Your solution filters SUM(Sales) by Product > 50 in the first set and then Category = {'E'} in the second set. My question filters SUM(Sales) by Product > 50 (same as yours) in the first set and Category = {'E'} AND SUM(Sales) by Product > 20 in the second.

Do you know if it is possible to add this second filter condition to the second set?

Re: Expression that compares difference of two sets based on filtering with aggregation

Lisa_P — Thu, 26 Jun 2025 04:55:28 GMT

Sorry, I must have copied and forgot to change, yes please change the second one.

Re: Expression that compares difference of two sets based on filtering with aggregation

NattiNatey — Mon, 30 Jun 2025 21:12:11 GMT

I added the second filter and this worked 🙂

{Group 1}Count({<Product={"=Sum(Sales)>50"}>*<Product ={"={<Category={E}>}Sum(Sales)>20"}>}Product)

{Group 2}Count({<Product={"=Sum(Sales)>50"}>*<Product ={"={<Category={E}>}Sum(Sales)>20"}>}Product)