7 Replies Latest reply: May 7, 2014 6:34 PM by Sophia Carmien RSS

    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?