topic Re: Set analysis with field value exclusion in QlikView

Set analysis with field value exclusion

NickP_DF — Thu, 21 Sep 2023 05:59:47 GMT

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

Re: Set analysis with field value exclusion

Alexparkes — Mon, 25 Sep 2023 04:47:42 GMT

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.

Re: Set analysis with field value exclusion

NickP_DF — Mon, 25 Sep 2023 07:43:56 GMT

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.