Set analysis with P() per row

[justalkak]

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?

[PrashantSangle]

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

[justalkak]

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

[Chanty4u]

Try this 

Count(DISTINCT  

    Aggr(  

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

        A_ID, Category  

    )

)

[marcus_sommer]

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.