topic Re: Set Analysis in QlikView

Set Analysis

Wed, 05 Feb 2014 08:13:48 GMT

Hi All,

I am trying to get my head around set analysis. Tom Mackay has created a very useful pdf on set analysis within QlikView (attached). It discusses the three elements of set analysis (Identifiers, Operators and Modifiers).

In his example he shows a couple of examples for Identifiers:

sum({1} Sales)

Then a couple of examples with operators and identifiers:

sum({1 - $} Sales)

Then a couple of examples with modifiers.

sum({1<Region = {US}>} Sales)

The document doesn't seem to have any that include both operators and modifiers. Is this possible? For example can I create an expression that is the Sum of all sales within the [Region] "US" and aren't [SalesProductType] of "Toys"?

I'm just trying to make sure I fully grasp the concept and wanted to check if this type of expression is possible?

Thanks

Dan

Re: Set Analysis

Carlos_Reyes — Wed, 05 Feb 2014 22:53:55 GMT

Yeah, I think your expression would be the next:

sum({1<Region = {US}, [SalesProductType -={'Toys'}]>} Sales)

Note that there is a minus " - " symbol before the equal in the [SalesProductType] modifier. That indicates all possible values except 'Toys'.

Re: Set Analysis

rwunderlich — Wed, 05 Feb 2014 23:34:52 GMT

The answer to your question re Region=US AND SalesProductType<>Toys doesn't involve any set operators. Just two modifiers.

{<Region={US},SalesProductType-={Toys}>}

However, since you were fishing for a syntax that mixes operators and modifiers:

{$<Region={US}> - $<SalesProductType={Toys}>}

-Rob

Re: Set Analysis

jaimeaguilar — Wed, 05 Feb 2014 23:47:41 GMT

Hi,

I think Set Analysis is one of the most complex things within QlikView. Besides, sometimes syntax is not that "friendly" at all.

About what you ask, it can be done with Set Analysis like this:

sum({$<Region = {'US'}, SalesProductType = {'*'} - {'Toys'}>} Sales)

alternately,

sum({$<Region = {'US'}, SalesProductType = SalesProductType - {'Toys'}>} Sales)

sum({$<Region = {'US'}, SalesProductType -= {'Toys'}>} Sales)

Note that 2nd and 3rd solution will underline code as if it was a mistake, however is a bug with syntax checker

Also remember that depending on what you want to calculate, you may use $ for current selections, or 1 for universe,

regards

Re: Set Analysis

Wed, 05 Feb 2014 23:50:29 GMT

Yes!

WIth a set analysis like this:

sum({$<Region={'US'},SalesProsuctType -={'Toys'}>}Sales)

Hope this helps

MArio

Re: Set Analysis

Wed, 05 Feb 2014 23:54:36 GMT

Look at this document for other information about set analysis.

http://iqlik.wordpress.com/2010/09/11/the-magic-of-set-analysis-syntax-and-examples/

Regards

Mario

Re: Set Analysis

maxgro — Thu, 06 Feb 2014 00:28:29 GMT

try with sum({$ <Region={"US"},SalesProductType=-{"Toys"}>} Expression1)

and see here

Set Analysis: syntaxes, examples

Re: Set Analysis

Thu, 06 Feb 2014 00:35:27 GMT

Hi,

yes you can do it.

Please investigate deeper what p() and e() funtions are in SetAnalysis.

IMHO:Very good and well described examples are in QV HELP under titel SET ANALYSIS

hope it helps

Re: Set Analysis

iktrayanov — Thu, 06 Feb 2014 00:42:43 GMT

Try this

Sum({<Region = {US},SalesProductType=SalesProductType - {Toys}>}sales)

Go to QlikView Help ant open the Set Analysis page there are plenty of examples there.

Re: Set Analysis

Sokkorn — Thu, 06 Feb 2014 01:01:59 GMT

Hi Dan,

Yes it possible. Something like this Sum({1<Region = {'US'}, [SalesProductType] -= {'Toys'}>} Sales)

See page 10, - sign mean exclude.

Regards,

Sokkorn

Re: Set Analysis

johnca — Thu, 06 Feb 2014 04:00:58 GMT

Hi Daniel,

This simple set analysis should do just what you asked (I think); Sum of all Sales in the US that are not Toys.

Sum({<Region = {'US'},SalesProductType -= {'Toys'}>} Sales)