Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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'.
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'.
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
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
Yes!
WIth a set analysis like this:
sum({$<Region={'US'},SalesProsuctType -={'Toys'}>}Sales)
Hope this helps
MArio
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
try with sum({$ <Region={"US"},SalesProductType=-{"Toys"}>} Expression1)
and see here
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
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.
Hi Dan,
Yes it possible. Something like this Sum({1<Region = {'US'}, [SalesProductType] -= {'Toys'}>} Sales)
See page 10, - sign mean exclude.
Regards,
Sokkorn