Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
NickP_DF
Creator II
Creator II

Set analysis with field value exclusion

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

 

Labels (1)
2 Replies
Alexparkes
Contributor
Contributor

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
Creator II
Creator II
Author

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.