Help in set analysis

[ali_hijazi]

Dears got the following data

LOAD * Inline [

DEFECT_ID, DEV_PLAN_ID, STATUS, DATA_SOURCE

X,1,TESTED,CRM

X,2, CANCELLED, CRM

X,1,NOT TESTED, DW

X,2, CANCELLED, DW

];

I have a pivot table with the two dimensions Defect_id, and Dev_plan_id

but what I want to show is the Status of the CRM data source when the the DW data source of the same defect id and dev plan id has a different status

i.e.

the pivot table will be showing from the above data the following

X, 1, TESTED

I put the following expression

only(AGGR(ONLY({<DATA_SOURCE={'CRM'}, DEFECT_ID={"=COUNT(DISTINCT STATUS)>1"}>} STATUS),DEFECT_ID,DEV_PLAN_ID))

but I got X,1, tested

               2, Cancelled

PLEASE ADVISE

[NickHoff]

You wouldn't use set analysis for this expression because there is no aggregation being performed.  What you could do is an if statement.

=IF(DEFECT_ID =- DEV_PLAN_ID, STATUS = 'TESTED', 'CANCELED')

[ali_hijazi]

no I can't use if because I have plenty of statuses

this is a sample data that I showed

[maxgro]

maybe

if(max(aggr(count (distinct STATUS), DEFECT_ID, DEV_PLAN_ID))>1,

only({$ <DATA_SOURCE={CRM}>} STATUS)

)

[NickHoff]

  I dont' understand your logic behind the status, but If you nest your if statements with the correct logic for the status it should work.

=IF(DEFECT_ID =- DEV_PLAN_ID AND DATA_SOURCE = 'CRM' , STATUS = 'TESTED', 'CANCELED'),

=IF(DEFECT_ID =- DEV_PLAN_ID AND DATA_SOURCE = 'DW' , STATUS = 'NOT TESTED', 'CANCELED'),

*/Else Statement

[gmoraleswit]

try this:

if(Only({$<DATA_SOURCE={'CRM'}>} STATUS) <> Only({$<DATA_SOURCE-={'CRM'}>} STATUS), Only({$<DATA_SOURCE={'CRM'}>} STATUS),null())

[ali_hijazi]

I tried this but it gave me allocated memory exceeded for the chart

[ali_hijazi]

that's why I'm trying to avoid if else statement

[gmoraleswit]

Here is another solution,

I created a key field with DEFECT_ID and DEV_PLAN_ID and use this field in the expression:

only({$<DATA_SOURCE={'CRM'},key={"=count(distinct STATUS)>1"}>} STATUS)

see attach