topic Re: Aggr() and Set Analysis in QlikView

Aggr() and Set Analysis

Wed, 03 Jul 2013 15:58:02 GMT

Hi all,

I'm looking for a way to select a 'bucket' in "Data Use Breakdown" (0 KB, for instance - these buckets were created at load time based on data use for each phone number, and the number on the data point represent # of phone lines that fall in that bucket), see the phone numbers updated in "Data Detail by User", but then (here's the tricky part) make "Data Use by Month" update to show the data used over the months available based on the phone numbers that have ever had 0KB usage.

What "Data Use by Month" does right now is sum total usage based on what is selected. Therefore, if I select the "0KB" bucket, it shows a flatline at 0KB. I want to select the "0KB" bucket and have "Data Use by Month" sum the usage over the past 3 months for thos phone numbers that have 0KB usage (probably not a flat line at 0KB). I'm thinking I need some sort of set anaylsis where I use aggr() like:

Aggr(SUM(DataUsageKB), DataUsageBands) ---> [DataUsageBands is the dimension on the graph, "Data Usage Breakdown"]

But I'm not sure exactly how it would look. I know this explanation is a bit tough to follow, so please ask clarification quesitons if needed!

Re: Aggr() and Set Analysis

swuehl — Wed, 03 Jul 2013 16:08:21 GMT

Look into the p() function in a set analysis field modifier:

Set Modifiers with Implicit Field Value Definitions

In the above examples, all field values have been explicitly defined or defined through searches. There is however an additional way to define a set of field values by the use of a nested set definition.

In such cases, the element functions P() and E() must be used, representing the element set of possible values and the excluded values of a field, respectively. Inside the brackets, it is possible to specify one set expression and one field, e.g. P({1} Customer). These functions cannot be used in other expressions:

Examples:

sum( {$<Customer = P({1<Product={‘Shoe’}>} Customer)>} Sales )
returns the sales for current selection, but only those customers that ever have bought the product ‘Shoe’. The element function P( ) here returns a list of possible customers; those that are implied by the selection ‘Shoe’ in the field Product.

sum( {$<Customer = P({1<Product={‘Shoe’}>})>} Sales )
same as above. If the field in the element function is omitted, the function will return the possible values of the field specified in the outer assignment.

sum( {$<Customer = P({1<Product={‘Shoe’}>} Supplier)>} Sales )
returns the sales for current selection, but only those customers that ever have supplied the product ‘Shoe’. The element function P( ) here returns a list of possible suppliers; those that are implied by the selection ‘Shoe’ in the field Product. The list of suppliers is then used as a selection in the field Customer.

sum( {$<Customer = E({1<Product={‘Shoe’}>})>} Sales )
returns the sales for current selection, but only those customers that never bought the product ‘Shoe’. The element function E( ) here returns the list of excluded customers; those that are excluded by the selection ‘Shoe’ in the field Product.

So your expression for data use by month might look like

=sum({<PhoneNumber = p({<DataUsageBands = {0}>}), DataUsageBands= >} DataUsageKB)

Re: Aggr() and Set Analysis

Wed, 03 Jul 2013 16:26:43 GMT

This is awesome! I wasn't familiar with the P() and E() functions. Is it possible to use GetFieldSelections() in this set analysis expression? Something like:

=sum({<PhoneNumber = P({<DataUsageBands = {"=GetFieldSelections(DataUsageBands)"}>}), DataUsageBands= >} DataUsageKB)

Not sure if that syntax is correct. Also, what does the empty "DataUsageBands=" do?

Thanks,

Paul

Re: Aggr() and Set Analysis

swuehl — Wed, 03 Jul 2013 16:39:44 GMT

Just try this to keep the PhoneNumber sensitiv to user selections:

=sum({<PhoneNumber = P(), DataUsageBands= >} DataUsageKB)

The DataUsageBands= ignores user selection in this field, otherwise you will still limit your record set to the selected DataUsage.

Re: Aggr() and Set Analysis

Wed, 03 Jul 2013 16:58:48 GMT

YESS!! She works like a charm. Thanks very much for your help.

-Paul