On this submit, we’ll evaluate two elusive strategies inside Qlik by which key enterprise questions will be addressed: Qlik Set Evaluation and Qlik Set Operations.

A typical enterprise goal is to broaden product gross sales or decide strategic effectiveness.  These issues typically take a kind like one of many following questions and are requested with an eye fixed towards historic efficiency.

• Which of my present prospects bought my product?
• Which of my present shoppers are benefitting from my applications?

Qlik gives an array of instruments to help within the solutions to those questions.  We are going to use Qlik Set Evaluation to determine prospects with particular traits or behaviors after which mix this with Qlik Set Operations to additional perceive the place we would count on alternatives.

Qlik Set Evaluation

Our pattern information set is a listing of fictitious prospects and their orders.  We all know their geographic particulars and their order historical past.  From right here we will start to glean some historic tendencies and goal habits, geographic or different attribute information from which to determine extra gross sales alternatives.

Let’s start by figuring out these prospects buying bikes.  Utilizing Qlik Set Evaluation we will determine these prospects who’ve bought bikes up to now.  A technique to do that is the next:

COUNT( { $ <PRODUCTLINE={"Bikes"}> } Distinct CUSTOMERNAME)

Within the desk beneath we see the client’s identify, a depend of shoppers and a depend of shoppers who’ve bought bikes.

Negating this, we would then anticipate finding these prospects NOT buying bikes.

COUNT({$<PRODUCTLINE-={"Bikes"}>} Distinct CUSTOMERNAME)

We see the two^nd and three^rd measure columns above are usually not mutually unique.  Why is that this? 

What’s being recognized within the set are the ORDERS moderately than the CUSTOMERS and whereas that is equal for the primary case, it’s clearly not for its negation within the second case. 

A more practical methodology to attain this and retain the flexibility to successfully determine the complimentary set is to make use of the P() and E() capabilities supplied by Qlik for this goal.

As a substitute of:

COUNT( { $ <PRODUCTLINE={"Bikes"}> } Distinct CUSTOMERNAME)

We use:

COUNT({$<CUSTOMERNAME=P({<PRODUCTLINE={"Bikes"}>})>}Distinct CUSTOMERNAME)

That is learn as ‘Which prospects have EVER bought bikes’ the place P() signifies Attainable.

To realize the complimentary set of these prospects who’ve NEVER bought bikes [where E() indicates Excluded] we will do one of many following:

                COUNT({$<CUSTOMERNAME=E({<PRODUCTLINE={“Bikes”}>})>}Distinct CUSTOMERNAME)

– OR –

COUNT({$<CUSTOMERNAME-=P({<PRODUCTLINE={“Bikes”}>})>}Distinct CUSTOMERNAME)

We are able to now observe that for each buyer they both HAVE or HAVE NOT bought bikes.  (Notice – as written, the Set Evaluation will retain context of any dimensional alternatives because of the $ notation).  As affirmation of this truth, we will see that the sum of the 2 teams (49 + 43) sum to the whole (92).

Qlik Set Operations

Because it stands, this may be helpful, nonetheless the strategies’ worth is amplified when mixed with different units by way of Qlik Set Operations.

COUNT({$
                <CUSTOMERNAME=P({<PRODUCTLINE={"Bikes"}>})>
    *
<CUSTOMERNAME=P({<PRODUCTLINE={"Planes"}>})> 
    } Distinct CUSTOMERNAME)

The Bike set factor is multiplied (*) with the Planes set factor to present us the intersection of those two units.  On this case, we’ve these prospects who’ve EVER bought each Bikes AND Planes.  We are able to then rapidly manipulate the units to reply which ever questions we’d wish to pose.

Which prospects have EVER bought bikes, however NEVER bought Planes?

COUNT({$
                <CUSTOMERNAME=P({<PRODUCTLINE={"Bikes"}>})>
    *
<CUSTOMERNAME=E({<PRODUCTLINE={"Planes"}>})>    
    } Distinct CUSTOMERNAME)

Alternatively:

COUNT({$
                <CUSTOMERNAME=P({<PRODUCTLINE={"Bikes"}>})>
    -
<CUSTOMERNAME=P({<PRODUCTLINE={"Planes"}>})>    
    } Distinct CUSTOMERNAME)

Qlik Set Operations Abstract

Combining Qlik Set Evaluation and Qlik Set Operations

If, as an alternative of in search of easy attribute identifiers, we want to perceive behavioral thresholds, i.e., Gross sales above $175k, we will leverage search in a extra superior Qlik Set Evaluation.

SUM({$<CUSTOMERNAME=P({<CUSTOMERNAME={"=SUM(SALES)>=175000"}>})>} SALES)

This may be additional altered and mixed by way of Qlik Set Evaluation Features P() and E() and Qlik Set Operations (* and -) to determine a really particular subset of shoppers for potential evaluation.

These prospects…

SUM( {$
                // by no means having over 175k in gross sales (see E() exclude perform beneath)
                <CUSTOMERNAME=E({<CUSTOMERNAME={"=SUM(SALES)>=175000"}>})>
     *
// who've ever bought Planes (see P() doable perform beneath, * operator above)
    <CUSTOMERNAME=P({<PRODUCTLINE={"Planes"}>})>
     -
//however are usually not situated in USA or Australia (see subtraction operator above)
    <CUSTOMERNAME=P({<COUNTRY={"USA","Australia"}>})>
    } SALES)

See the ‘Mixed’ column beneath for the gross sales of the required set of shoppers.

We now have the flexibility to ask and reply questions which might goal subsets of shoppers primarily based on any attribute or habits and which will be simply and reliably manipulated with out prolonged or advanced modifying.