Set Analysis

[#1]

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

[Carlos_Reyes · Accepted solution]

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'.

[rwunderlich]

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

[jaimeaguilar]

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)

or

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

[#5]

Yes!

WIth a set analysis like this:

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

Hope this helps

MArio

[#6]

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

[maxgro]

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

and see here

Set Analysis: syntaxes, examples

[#8]

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

[iktrayanov]

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.

[Sokkorn]

Hi Dan,

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

See page 10, - sign mean exclude.

Regards,

Sokkorn