topic Re: is my set analysis is correct? Please help in QlikView

is my set analysis is correct? Please help

ananyaghosh — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a if function with the following conditions:

if((not match([Product Type],'DE','PA','RE','SA','CE','AA','VA','5M','FA') and

[Sales Trn Detail DCIO TRADE IND] =1 and [Trans Trade Class]='S' and not match([Trans Status Code],'D')), sum([Sales Amount USD]))

and I have replaced it with the following set analysis:

sum({$<[Product Type]-= {'DE','PA','RE','SA','CE','AA','VA','5M','FA'},[Sales Trn Detail DCIO TRADE IND]={1},[Trans Trade Class]={'S'},[Trans Status Code] -= {'D'}>}[Sales Amount USD])

and they gives me different result for me.

Please help me if anything is wrong in my set analysis.

Thanks,

Sandip

Re: is my set analysis is correct? Please help

Anil_Babu_Samineni — Tue, 20 Jun 2017 06:46:00 GMT

Try this?

sum({$<[Product Type]-= {'DE','PA','RE','SA','CE','AA','VA','5M','FA'},[Sales Trn Detail DCIO TRADE IND]={1},[Trans Trade Class]={'S'}>+<[Trans Status Code] -= {'D'}>} [Sales Amount USD])

Re: is my set analysis is correct? Please help

avinashelite — Tue, 20 Jun 2017 06:48:31 GMT

Try like this

sum({$<[Product Type]= {"*"}-{'DE','PA','RE','SA','CE','AA','VA','5M','FA'},[Sales Trn Detail DCIO TRADE IND]={1},[Trans Trade Class]={'S'},[Trans Status Code] = {"*"}- {'D'}>}[Sales Amount USD])

Re: is my set analysis is correct? Please help

Kushal_Chawda — Tue, 20 Jun 2017 07:04:32 GMT

It's always better to create the Flag in script for long conditions to make expression simpler

LOAD *,

if((not match([Product Type],'DE','PA','RE','SA','CE','AA','VA','5M','FA') and

[Sales Trn Detail DCIO TRADE IND] =1 and [Trans Trade Class]='S' and not match([Trans Status Code],'D')), 1) as Flag

Expression:

=Sum({<Flag={1}>} [Sales Amount USD])

Re: is my set analysis is correct? Please help

antoniotiman — Tue, 20 Jun 2017 07:41:12 GMT

Hi Sandip,

may be because Set Analysis is per chart, non per Row.

If() is per Row.

Regards,

Antonio

Re: is my set analysis is correct? Please help

nsm1234567 — Tue, 20 Jun 2017 08:31:02 GMT

Hi There,

This could be happening for a number of reasons. Some suggestions:

1) Are any of your [Product Type] or [Trans Status Code] values (or fields associated with them) nulls ? -= in Set Analysis will work differently to "not match" in these cases

2) Are you sure that the Product Type values, for instance, are not repeated in a way you're not anticipating? I've attached an example of how similar expressions can "go wrong" based on the underlying data.

The particular way in which your result is wrong, or some sample data would be helpful. Is the Set Analysis returning a lower value than you're expecting? A higher value?

Re: is my set analysis is correct? Please help

sunny_talwar — Tue, 20 Jun 2017 08:59:47 GMT

Are there nulls in Product Type or Trans Status Code fields?

Re: is my set analysis is correct? Please help

sasiparupudi1 — Tue, 20 Jun 2017 12:12:04 GMT

May be try

sum({1<[Product Type]-= {'DE','PA','RE','SA','CE','AA','VA','5M','FA'},[Sales Trn Detail DCIO TRADE IND]={1},[Trans Trade Class]={'S'},[Trans Status Code] -= {'D'}>}[Sales Amount USD])