Set Analysis Internal use only What is a

Set Analysis Internal use only

What is a “SET” • Collection of objects, things or symbols which are clearly defined. • such a set of books, • A group of students, • A list of states in a country, • A collection of cards, etc. Internal use only 2

Significance in BI • Basically we define set of “Selection”. Example: • A range of dates can be defined as set. • A group of users, companies etc can be selected as a set. Internal use only 3

Significance in BI (Contd. . ) • We generally define expression as: if(year=2008, sum(Sales) ) • But an alternative to this approach is: sum( {$<Year={2008}>} Sales ) (will explain later) • Provides a method of defining groups (sets) of information that are independent of the current selections. Internal use only 4

Properties • Can create sets based on other sets. • Must be used in aggregation function. • Always begin and end with curly brackets {}. Internal use only 5

Advantage • Very powerful functionality for comparison analysis Example: • This year vs. last year. For last year sum( {$<Year = {$(=Only(Year)-1)}>} Sales) • Products purchased vs. not purchased. Internal use only 6

Advantage (Contd. . ) • Eliminate the need for additional, complex coding within an application. • Provides much more flexibility. (Expressions can be added for data outside of your current selection criteria) Internal use only 7

Syntax Categories: • Identifiers • Operators • Modifiers Internal use only 8

Identifiers: • 0 - Represents an empty set. • 1 - Represents the full set of all the records in the application. sum({1} Sales) • (Represents total sales disregarding selections) • $ - Represents the records of the current selection. • sum({$1} Sales) sum(Sales) Internal use only 9

Identifiers • $1 - Represents the previous selection sum({$1} Sales) • $_1 - Represents the next (forward) selection. • Bookmark 01 - Represents the bookmark ID or the bookmark name sum({Bookmark 01} Sales) Internal use only 10

Operators • Union (+) sum({ ($ + Bookmark 01)} Sales) • Exclusion (-) sum({1 - $} Sales) • Intersection (*) sum({$ * Bookmark 01} Sales) • Symmetric Difference (/) (either, but not both) Internal use only 11

Modifier: • Set expression can be modified by modifier. • The modifier consists of field names, each followed by selection(s). • Modifiers begin and end with angle brackets <>. Internal use only 12

Modifiers (Contd. . ) • sum({$<Order. Date = Delivery. Date>} Sales) Returns the sales for the current selection where Order. Date = Delivery. Date. • sum({1<Region = {US}>} Sales) Returns the sales for region US disregarding the current selection Internal use only 13

Modifiers (Contd. . ) • sum({$<Region = >} Sales) Returns the sales for current selection, but with the selection in “Region” removed. • sum({<Region = >} Sales) Returns same as the example above. When the set to modify is omitted, $ is assumed. Internal use only 14

Modifiers (Contd. . ) Dollar sign expansion: • sum( {$<Year = {$(v. Last. Year)}>} Sales ) (using variable) • sum( {$<Year = {$(=Only(Year)-1)}>} Sales ) (Previous Year Data) Internal use only 15

Example Internal use only 16

Indirect Set Analysis It is now possible to state selections in a field based on selections in another field. Functions Internal use only 17

Indirect Set Analysis sum({$<Company. Name = p({$<Category. Name={'Baby Clothes'}>} Company. Name)>} Sales) Sales for Customers who purchased Baby. Clothes. The set p({}) returns the values of Company. Name (Customer) that purchased Baby Clothes. Internal use only 18

{Thanks} Internal use only 19