Expression with set analysis gives wrong calculation

[amber2000]

Hi all,

I'm struggling with an expression that includes set analysis.

I want to calculate the amount of colli and by using the set analysis statement I want to exclude some Articles

= sum({$<[WH_Cd] -= {12}, [Rec_Article]={">99999","<>4859696"}, [Aisle_Facturation] -= {DISPLAY, EMPTIES}>}[Rec_Colli])

This works but when I want to exclude another Rec_Article the calculations are wrong.

= sum({$<[WH_Cd] -= {12}, [Rec_Article]={">99999","<>4859696","<>5797143"}, [Aisle_Facturation] -= {DISPLAY, EMPTIES}, [Vendor_Nbr] -= {"0014038"}>}[Rec_Colli])

I've calculated all the colli for Rec_Article 5797143 and the sum is 1340.

This should be extracted from the total on dimension Aisle_Desc.

This should be done for 2 expressions: #Colli and #Pallets

This is the situation before the exclude of Rec_Article 5797143 (which belongs to Aisle_Desc 'Mosselen_Zuurwaren')  

Aisle_Desc	#Colli	#Pallets	Avg_IN
GRF	422.379	10.033	42,10
Industrieel_Brood	22.869	968	23,63
Mosselen_Zuurwaren	45.343	1.011	44,85
PAT	2.420	170	14,24
PBL_Traiteur	214.306	6.525	32,84
PBL_Vlees	192.311	9.933	19,36
PBL_Zuivel	543.287	17.564	30,93
PBS_Traiteur	77.681	1.795	43,28
Zuivel_Gekoeld	196.775	2.715	72,48
Zuivel_Ongekoeld	88.598	3.005	29,48
Total	1.805.969	53.719	33,62

The sum for Rec_Article 5797143 is

Aisle_Desc	#Colli	#Pallets	Avg_IN
Mosselen_Zuurwaren	1.340	3	446,67
Total	1.340	3	446,67

So the situation after the exclude should be:   

Aisle_Desc	#Colli	#Pallets	Avg_IN
GRF	422.379	10.033	42,10
Industrieel_Brood	22.869	968	23,63
Mosselen_Zuurwaren	44.003	1.008	43,65
PAT	2.420	170	14,24
PBL_Traiteur	214.306	6.525	32,84
PBL_Vlees	192.311	9.933	19,36
PBL_Zuivel	543.287	17.564	30,93
PBS_Traiteur	77.681	1.795	43,28
Zuivel_Gekoeld	196.775	2.715	72,48
Zuivel_Ongekoeld	88.598	3.005	29,48
Total	1.804.629	53.716	33,60

And this is the result I'm getting and I've highlighted the calculations that are wrong in red:

Aisle_Desc	#Colli	#Pallets	Avg_IN
GRF	413.437	9.918	42,10
Industrieel_Brood	22.869	968	23,63
Mosselen_Zuurwaren	42.938	978	43,90
PAT	2.420	170	14,24
PBL_Traiteur	214.306	6.525	32,84
PBL_Vlees	192.149	9.913	19,38
PBL_Zuivel	542.349	17.520	30,96
PBS_Traiteur	77.441	1.791	43,24
Zuivel_Gekoeld	196.775	2.715	72,48
Zuivel_Ongekoeld	88.598	3.005	29,48
Total	1.793.282	53.503	33,52

Does anyone know what I'm doing wrong please?

Kind regards,

Monique

[sunny_talwar · Accepted solution]

Try this

=Sum({$<[WH_Cd] -= {12}, [Rec_Article]={">99999"}-{'4859696', '5797143'}, [Aisle_Facturation] -= {DISPLAY, EMPTIES}>}[Rec_Colli])

[amber2000]

Thanks again Sunny,

Why does the expression [Rec_Article]={">99999","<>4859696","<>5797143"} not work?

This worked fine: [Rec_Article]={">99999","<>4859696"}

Monique

[sunny_talwar]

I am not sure, but may be swuehl‌ can answer this

[swuehl]

Are you 100% sure this one worked?

[Rec_Article]={">99999","<>4859696"}

I can imagine this does also not work as requested, for several reasons.

[amber2000]

I'm not 100% sure but it must work because I can't find any data for that set.

Can you also tell me what the reasons are that it can't work please?

[swuehl]

a) The field value list of your set modifier combines the elements with OR logic, not AND logic.

Your first element is a search that returns values larger than 99999, your second search is suppossed to exclude 4859696 (or to return any other value).

This can't work as intended with the default OR logic (if I understand your intention correctly).

b) a search "<>4859696" does not mean 'not equal 4859696'.

It's a numeric search "<limit_1_numerical_value>limit_2_numerical_value"

where you are missing the limit_1_numerical_value. I believe that Qlik will just ignore the first comparison operator then, so your search should equal ">4859696".

If you would use "<4859696>4859696", then this may return values to the value list excluding 4859696, but still finding a) would put 4859696 back in the list due to the first search that returns all values larger than 99999 (assuming that 4859696 is a possible field value).

You should be able to test both findings with some numeric sample data.

[sunny_talwar]

Now that you have explained it in great detail... it all makes sense. Thanks swuehl‌

[amber2000]

Sorry for the late reaction (I was on holiday).

Thank you Stefan for the detailed explanation.

For the less experienced developers this is very useful and educative information.

Also thanks to you Sunny for bringing this to Stefan's attention.

Kind regards,

Monique