Set analysis with not equal clause

[NickP_DF]

Hello guy's,

In a dimension of a pivot table I'd like to mark the CodiceODL rows in which the derivated 1->n table have almost 1 row with these conditions:

FaseInLavorazione='S'

LavorazioneConclusa='N'

CodLavorazione <>'LAA' and CodLavorazione  <> 'V000'

I've tried this expression:

Not IsNull(Aggr(Sum({$<CodLavorazione-={'LAA'}, CodLavorazione-={'V000'}, FaseInLavorazione={'S'}, LavorazioneConclusa={'N'}>} 1), CodiceODL))

But it returns me a true value in this case:

CodLavorazione / InLavorazione / LavorazioneConclusa

LAA / S / N
ETC0 /S / N
VC000 / S / N

Could anyone help me in writing the correct syntax expression?

Thank you so much.

[Kushal_Chawda]

@NickP_DF  try below

Aggr(only({$<CodLavorazione-={'LAA'}, CodLavorazione-={'V000'}, FaseInLavorazione={'S'}, LavorazioneConclusa={'N'}>}CodiceODL), CodiceODL,CodLavorazione,CodLavorazione,FaseInLavorazione,LavorazioneConclusa)

Note: check "supress null values" option for this calculated dimension

[NickP_DF]

Hello @Kushal_Chawda 

Thank you for the response: your function gives me the CodiceODL for the rows where there is a FaseInLavorazione='S' and LavorazioneConclusa='N' (both in the case with CodLavorazione='LAA' and not); it returns no-rows for the cases where there isn't any FaseInLavorazione='S' and LavorazioneConclusa='N', so it doesn't works as I need 😞

Maybe I was not clear enough: I need a boolean function (or a 'S'/'N' resulting function) which marks true (or 'S') the cases where, reading all the phases of an CodiceODL (in relashionship 1-n) there is at least one with all this conditions verified simultaneously :

CodLavorazione <>'LAA' and CodLavorazione  <> 'V000' and FaseInLavorazione='S' and LavorazioneConclusa='N'

I'll give some examples:

1 - CodiceODL 4540 (expected function value = True)

CodLavorazione / InLavorazione / LavorazioneConclusa
LAA / N / N
CTRS/ S / N (The valid phase is in progress, thus I have to consider it)
V00 / N / N

2 - CodiceODL 4932 (expected function value = False)

CodLavorazione / InLavorazione / LavorazioneConclusa
LAA / S / N (The phase in progress is not valid, thus I don't have to consider it)
ZINC / N / N
V00 / N / N

3 - CodiceODL 4009 (expected function value = False)

CodLavorazione / InLavorazione / LavorazioneConclusa
LAA / N / N
ZINC / S / S  (The valid phase is closed, thus I don't have to consider it)
V00 / N / N

2 - CodiceODL 4942 (expected function value = False)

CodLavorazione / InLavorazione / LavorazioneConclusa
LAA / S / N (The phase in progress is not valid, thus I don't have to consider it)
DENT/ N / N
V00 / S / N (The phase in progress is not valid, thus I don't have to consider it)

Hope it's clear now.

Thanks.

N.

[Kushal_Chawda]

@NickP_DF  still this not clear to me.. Sample data with expected output will help here.

[NickP_DF]

@Kushal_Chawda 

I've attached and example: hope it is much clearer now.

Pls, let me know if there is something other you need to help me.

Thank you so much.

N.