topic Re: set analysis expression in QlikView

set analysis expression

geogou1973 — Thu, 10 Nov 2016 14:24:34 GMT

Hello,

I have a field that has values like NA, NC, NZ and values null.

How can i take only the null values in an expression of set analysis ?

sum({<isnull(CSTAAR)>} [Stock Qty]) this does not work

sum({<CSTAAR={' '}>} [Stock Qty]) this does not work also.

please any help ?

Thank you in advance.

Re: set analysis expression

sunny_talwar — Thu, 10 Nov 2016 14:29:03 GMT

May be create a flag in the script:

LOAD CSTAAR,

[Stock Qty],

If(Len(Trim(CSTAAR)) = 0, 1, 0) as Flag

FROM ....

and then this

Sum({<Flag = {1}>} [Stock Qty])

Re: set analysis expression

geogou1973 — Thu, 10 Nov 2016 14:46:43 GMT

Hello Sunny,

I used the following expression

sum({<CSTAAR=-{'NC','NA','NZ'}>} [Stock Qty])

Re: set analysis expression

sunny_talwar — Thu, 10 Nov 2016 14:48:44 GMT

If that worked, then this should also work:

Sum({<CSTAAR=-{'*'}>} [Stock Qty])

Re: set analysis expression

geogou1973 — Thu, 10 Nov 2016 14:55:35 GMT

Sunny thank you for your help.

In this formula of summarize the stock qty how can i have only the zero values ?

Re: set analysis expression

sunny_talwar — Thu, 10 Nov 2016 14:57:24 GMT

Not sure I understand you requirement?

Re: set analysis expression

geogou1973 — Thu, 10 Nov 2016 14:59:48 GMT

In the level of Item i have the following expression

sum({<CSTAAR=-{'NC','NA','NZ'}>} [Stock Qty])

some items have 0 (zero) values. How can i have only the zero values in the report ?

Re: set analysis expression

sunny_talwar — Thu, 10 Nov 2016 15:01:35 GMT

May be this:

If(Sum({<CSTAAR=-{'NC','NA','NZ'}>} [Stock Qty]) = 0, 1)

Re: set analysis expression

geogou1973 — Thu, 10 Nov 2016 15:11:08 GMT

Sunny i have the following data and i want in the report to isolate the data with stock value 0,000

Store	Store Descr	Mother	Mother Descr	Stock Qty
23	EFKARPIA	1140578	ΡΥΖΑΛΕΥΡΟ ΟΛΙΚΗΣ ΑΛ BIO BEBE 200ΓΡ	0,000
23	EFKARPIA	1195851	ΤΟΡΤΕΛΙΝΙ FORMAGGI BARILLA 500ΓΡ	0,000
23	EFKARPIA	1125148	ΑΠΟΡ ΠΛ ΑΝΟΙΞ DIXAN25M 1,75KG ΚΟΥΤ	0,000
23	EFKARPIA	1100115	ΑΦΡΟΛ ARGAN OIL&OR L/P MARSEIL 250	0,000
23	EFKARPIA	1182123	ΑΦΡΟΛΟΥΤΡΟ SMOOTH CREAM NIVEA 750ML	0,000

23	EFKARPIA	1150387	ΝΕΡΟ ΜΕΤΑΛΛΙΚΟ ΔΙΟΣ 500ML	6.609,000
23	EFKARPIA	1142495	ΝΕΡΟ ΑΥΡΑ 500ΜL PET	4.849,000
23	EFKARPIA	801224	ΑΡΤΟΠΑΡΑΣΚΕΥΑΣΜΑ ΑΣΠΡΟ 350Γ	3.137,000
23	EFKARPIA	987041	ΣΑΚΟΥΛΑ ΓΙΑ ΚΟΤΟΠΟΥΛΟ 170Χ80Χ355	3.000,000
23	EFKARPIA	435838	COCA COLA ΜΕΤ ΚΟΥΤ 6X330ML	2.690,000
23	EFKARPIA	121456	ΝΕΡΟ ΜΕΤΑΛΛΙΚΟ ΖΑΓΟΡΙ 500ML	2.405,000
23	EFKARPIA	991844	SPECIAL ΨΩΜΙ ΓΕΡΜ ΧΩΡΙΑΤ 1ΚΙΛΟ CARR	1.908,000
23	EFKARPIA	1064246	ΧΑΡΤΙ ΥΓΕΙΑΣ ΣΧΕΔ4Φ CARREF10T Χ131Γ	1.631,000
23	EFKARPIA	662976	ΦΡΟΥΤΟΠΟΤΟ ΠΟΡΤ ΜΗΛ ΒΕΡ ΑΜΙΤΑ 1LT	1.612,000
23	EFKARPIA	100860	ΓΑΛΟΠΟΥΛΑ ΚΤΨ ΜΕ ΘΕΡΜΟΜ DOUXΜΕΤ ΒΑΡ	1.575,799
23	EFKARPIA	697606	ΑΝΑΨΥΚΤΙΚΟ COCA COLA 2X1LT	1.494,000
23	EFKARPIA	1011698	ΑΛΕΥΡΙ 70% ΔΥΝΑΤΟ ΚΙΛΟΥ(ΣΑΚΙ)	1.475,000
23	EFKARPIA	936835	COCA-COLA 2X1,5LT	1.372,000
23	EFKARPIA	1221661	ΤΟΜΑΤΑ ΜΑΡΑΤΑ ΨΙΛΟΚ ΜΕΤ 280ΓΡ ΣΤ.Β	1.321,000
23	EFKARPIA	1221521	ΤΟΜΑΤΑ ΨΙΛ ΧΑΡΤ ΜΑΡΑΤΑ240Γ ΣΤΑΘ ΒΑΡ	1.239,000

Re: set analysis expression

sunny_talwar — Thu, 10 Nov 2016 15:14:26 GMT

Creating flags in the script might be the best way to handle this

LOAD Store,

Store Descr,

Mother,

Mother Descr,

Stock Qty,

If(Stock Qty = 0, 1, 0) as Flag

FROM...

and then use the flag in your set analysis

Re: set analysis expression

geogou1973 — Thu, 10 Nov 2016 15:24:37 GMT

also with the flag i don't have the 5 lines that have 0,000 values

Re: set analysis expression

sfatoux72 — Thu, 10 Nov 2016 15:30:42 GMT

Use this, :

If(Sum({<CSTAAR=-{'NC','NA','NZ'}>} [Stock Qty]) = 0, 0, Null())