Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggregating from a previous field within a load script

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

ProductTotal LicensesCustomer Number

Basic

12379
Complete304568
Basic1238524
Plus75557
Plus444535
Basic17896
Complete17565
Basic88966

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?

1 Solution

Accepted Solutions
nagaiank
Specialist III
Specialist III

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

Is this what you were looking for?

View solution in original post

7 Replies
nagaiank
Specialist III
Specialist III

Create a Straight table

Dimension: Product

Expression 1: Sum([Total Licenses])

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

Not applicable
Author

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?

nagaiank
Specialist III
Specialist III

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.

Not applicable
Author

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

nagaiank
Specialist III
Specialist III

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

Is this what you were looking for?

maxgro
MVP
MVP

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])

1.png

Not applicable
Author

Thanks!!!