topic Re: Aggregating from a previous field within a load script in QlikView

Aggregating from a previous field within a load script

Fri, 02 May 2014 21:53:52 GMT

Here's the first few lines of the table I am pulling my data from:

Product	Total Licenses	Customer Number
Basic	1	2379
Complete	30	4568
Basic	123	8524
Plus	7	5557
Plus	44	4535
Basic	1	7896
Complete	1	7565
Basic	8	8966

I would like to calculate what percent of total licenses are comprised of each product. For example, if I have 3,000 licenses for the product "Basic" and 10,000 licenses for all three products, then Basic would be 30% of consumer products. I need to use that 30% in other calculations.

I'm using the following to make a chart in my dashboard for expression "Percent Consumer". It works.

sum({<Product = {'Basic'}>/<Product = {'Plus'}>/<Product = {'Complete'}>}[Total Licenses])/

(sum(total {<Product = {'Basic'}>}[Total Licenses])+

sum(total {<Product = {'Plus'}>}[Total Licenses])+

sum(total {<Product = {'Complete'}>}[Total Licenses]))

What I'd really like to do is do the same kind of thing in my load script so I can use this value on other data I'm loading. I am trying the following:

if(Product='Basic',

lookup([Total Licenses],Product,'Basic','License_By_Product')/

(lookup([Total Licenses],Product,'Basic','License_By_Product')+

lookup([Total Licenses],Product,'Plus','License_By_Product')+

lookup([Total Licenses],Product,'Complete','License_By_Product')),

if(Product='Plus',

lookup([Total Licenses],Product,'Plus','License_By_Product')/

(lookup([Total Licenses],Product,'Basic','License_By_Product')+

lookup([Total Licenses],Product,'Plus','License_By_Product')+

lookup([Total Licenses],Product,'Complete','License_By_Product')),

if(Product='Complete',

lookup([Total Licenses],Product,'Complete','License_By_Product')/

(lookup([Total Licenses],Product,'Basic','License_By_Product')+

lookup([Total Licenses],Product,'Plus','License_By_Product')+

lookup([Total Licenses],Product,'Complete','License_By_Product'))

,0))) as [Percent Consumer]

It's outputting a number, but it's the wrong number. I believe the reason for this is that "lookup" is only looking at one line in the table above. What I need is a way to sum up all the lookup values for a product. I tried using "sum" or "aggr" in conjunction with "lookup", but either that doesn't work or I'm doing it wrong.

Any advice?

Re: Aggregating from a previous field within a load script

nagaiank — Fri, 02 May 2014 22:20:57 GMT

Create a Straight table

Dimension: Product

Expression 1: Sum([Total Licenses])

Expression 2: Sum([Total Licenses])/Sum(TOTAL [Total Licenses])

Re: Aggregating from a previous field within a load script

Fri, 02 May 2014 22:29:53 GMT

The straight table isn't the issue. I have been able to do that with no issues.

The reason I'm not suing Sum(TOTAL expression) above is that there are some Product values I don't want to include that aren't included in my table example above.

I need to get this value in my load script so I can use the percent when I load other data. How might I do that?

Re: Aggregating from a previous field within a load script

nagaiank — Fri, 02 May 2014 22:38:31 GMT

One option is to use a flag with values 1 or 0 for each product (if it is based on the product) depending on if you want to include or exclude a product and use

Sum(TOTAL (Flag *[Total Licenses]))

for getting the total.

If you give your complete requirements, you may get more useful answer from the forum.

Re: Re: Aggregating from a previous field within a load script

Fri, 02 May 2014 23:19:13 GMT

I am attaching an example of what I'm trying to describe. Maybe this will help explain what I'm looking for.

Re: Re: Re: Aggregating from a previous field within a load script

nagaiank — Sat, 03 May 2014 00:03:36 GMT

Updated application with some changes in load script and a chart with simpler expressions is attached.

Is this what you were looking for?

Re: Aggregating from a previous field within a load script

maxgro — Sat, 03 May 2014 06:17:42 GMT

flag the product in the script

License_By_Product:

LOAD [Product],

[Total Licenses],

[Customer Number],

if(Match(Product, 'Basic', 'Plus', 'Complete'), 1, 0) as Flag

FROM

Licenses.xlsx

(ooxml, embedded labels, table is Sheet1);

then use in your chart

expression =sum({$ <Flag={1}>} [Total Licenses])

Re: Re: Re: Aggregating from a previous field within a load script

Wed, 07 May 2014 22:34:31 GMT

Thanks!!!