Set analysis with field value exclusion

[NickP_DF]

Hello guys,

I have three tables:

Sales: DataVendita, keyVen

Warehouse movements: DataMovimento, QtaMovimentata, keyMmg

Calendar: DataAnalisi

I'd like to summarize the warehouse quantities under two conditions:

- the data of the warehouse movement is between the minimun and the maximum of the DataAnalisi selection

- the reference of the warehouse movement (KeyMmg) isn't inside the same selected range of  sales

for each DataVendita corresponding to one DataAnalisi in a pivot table, I built this expression in a pivot table:

Sum({$<DataMovMag={">=$(=Min(DataAnalisi))<=$(=Max(DataAnalisi))"}> } QtaMovimentata)

but I'm not able to integrate it with the second one:

keyMmg is not one of the keyVen in the entire above selected range

Hope my issue is clear enough, 'cause it's a bit hard to explain!

Thank you for the help.

Nick

[Alexparkes]

Hii, UTSA Blackboard

You can first filter the DataMovMag based on your date range. Then, you use the keyMmg -= {"*"} to exclude any keyMmg values that match any keyVen values within the selected date range. This will give you the desired summary of warehouse quantities meeting both conditions. 

[NickP_DF]

Thank you for the reply, Alex

I've tried your suggestion, if I understanded it properly:

=Sum({$<DataMovMag={">=$(=Min(DataAnalisi))<=$(=Max(DataAnalisi))"}, keyMmg -= {"*"}> } QtaMovimentata)

but it excludes all records with a valid keyMmg, not only the ones with the keyMmg related to the keyVen.

Am I missing something?

Pls, note that I have tried also this one (with wrong result):

=Sum({$<DataMovMag={">=$(=Min(DataAnalisi))<=$(=Max(DataAnalisi))"}, keyMmg-={"=(=keyVen)"}> } QtaMovimentata)

Thks.