topic Re: Set analysis with P() per row in App Development

Set analysis with P() per row

justalkak — Tue, 04 Mar 2025 13:20:33 GMT

Hi All,

I have the following data:

Table A:

Key, A_ID

A1, 123

A1, 234

A1, 345

...

Table B:

Key, B_ID

A1, 123

A1, 234

A1, 345

A1, 789

...

I wanted to build a table in the front end and compare how many A_Ids are in common with the B_Ids.

It worked with formula:

Count({<A_ID=P(B_ID)>} distinct A_ID) and was giving result of 3 (all A_Ids found within the B_IDs).

Now my Table A has additional field that I need to use in the front end:

Table A:

Key, A_ID, Category

A1, 123, X

A1, 234, X

A1, 345, Y

The previous formula doesnt work when I put Category as dimension to the straight table, as it looks on all possible B_Ids, not the ones matching the Category. It only gives the right result when 1 Category is filtered e.g. for X it will give then 2.

How to modify the formula so that it looks on the Category level too?

Re: Set analysis with P() per row

PrashantSangle — Tue, 04 Mar 2025 14:07:11 GMT

Hi,

I am using the same expression, I am getting correct result.

Dimension; Key, Category

Expression: Count({<A_ID=P(B_ID)>} distinct A_ID)

Result:

Regards,

Prashant Sangle

Re: Set analysis with P() per row

justalkak — Tue, 04 Mar 2025 14:14:38 GMT

In my data model it will show 3 next to X, and 2 only if I filter for X..

Re: Set analysis with P() per row

Chanty4u — Tue, 04 Mar 2025 17:46:49 GMT

Try this

Count(DISTINCT

Aggr(

If(A_ID = P({<Category=P(Category)>} B_ID), A_ID),

A_ID, Category

)

Re: Set analysis with P() per row

marcus_sommer — Thu, 06 Mar 2025 12:21:23 GMT

Set analysis performs a column-level evaluation, like a selection. Therefore it's not possible to consider any conditions which needs a row-level evaluation. To compare anything against the row-level it requires an if-loop.

Quite often it's very helpful to prepare such things already within the data-model, maybe just with an exists(Key) in the load.