6 Replies Latest reply: Feb 8, 2016 12:50 PM by Stefan Wühl RSS

    Nested set analysis?

      I want to do some analysis that counts the number of items sold to customers with a total spend between certain spend thresholds.

      E.g. Customers spending between 0-999 bought a total of 500 boxes of product A, 399 boxes of product B. Customers spending between 1000-1499 bought 300 boxes of product B etc.

      My understanding is that I need to do a set analysis expression for each threshold spend whilst using product as the dimension.

      I've got as far as writing an expression that looks like this:

      sum({$<location_id=P({<sum(threshold_value)={">0<1000"}>})threshold_quantity)

      I've done something wrong here but I'm not sure what!

      With your help what I'm hoping to produce is a simple table that shows all of our hundreds of products and a count of units sold by the various thresholds.

      Thanks for your help

       

       

        • Re: Nested set analysis?
          Stefan Wühl

          Not sure if I understand, could you maybe upload a small sample QVW?

           

          I was thinking about something like this (in a chart with dimension Product):

           

          =Count( {<Customer = {"=Sum(SpentValue) >= 0 and Sum(SpentValue) <1000"} >} DISTINCT BoxID)

            • Re: Nested set analysis?

              Thanks for your reply. I haven't got a small enough QVW to upload.

               

              The objective I'm trying to reach is a table that would have products listed down the left and columns that show each product's volumes by spend grouping. I wouldn't want to include the product itself within the expression as it's the dimension.

               

              In plain english this is what I want to get:

               

              How many boxes/units of each product we sell is purchased by customers spending between a and b, b and c, d and e.

               

              There is nothing in our data that currently groups customers by spend and so it would need to be done in the set analysis (unless there's another way?).

                • Re: Nested set analysis?
                  Stefan Wühl

                  My intention was to do this by creating a field modifier for Customer with an advanced search expression

                   

                  =Count( {<Customer = {"=Sum(SpentValue) >= 0 and Sum(SpentValue) <1000"} >} DISTINCT BoxID)

                   

                  You can add more expressions to select customers with different spend amount:

                   

                  =Count( {<Customer = {"=Sum(SpentValue) >= 1000 and Sum(SpentValue) <2000"} >} DISTINCT BoxID)

                  =Count( {<Customer = {"=Sum(SpentValue) >= 2000 and Sum(SpentValue) <5000"} >} DISTINCT BoxID)

                   

                  You can also use variables in the expressions:

                   

                  =Count( {<Customer = {"=Sum(SpentValue) >= a and Sum(SpentValue) <b"} >} DISTINCT BoxID)

                  =Count( {<Customer = {"=Sum(SpentValue) >= b and Sum(SpentValue) <c"} >} DISTINCT BoxID)

                  =Count( {<Customer = {"=Sum(SpentValue) >= c and Sum(SpentValue) <d"} >} DISTINCT BoxID)

                    • Re: Nested set analysis?

                      I've tried this as an example expression:

                      =sum({<location_id={"=sum(threshold_value)>1000 and sum(threshold_value)<1500"}>}threshold_quantity)

                      With the dimension as the SKU code of the products. There is some flaw with this though as when I add a second dimension which is the product description (a text field that describes the product) multiple descriptions are being assigned to each of the SKU codes when it should be 1 code to 1 description. So something can't be quite right?

                • Re: Nested set analysis?
                  Peter Cammaert

                   

                  sum({$<location_id=P({<sum(threshold_value)={">0<1000"}>})threshold_quantity)

                  I've done something wrong here but I'm not sure what!

                   

                  To me this looked like an implicit question, so I'll try to answer it.

                  Set modifiers (everything you put between < and > in a - in your case "nested" - set specification) change the data set for an expression by temporarily assigning other values to one or more listed fields. As a consequence, the equal sign in the middle is an assignment operator, not a comparison operator.

                  A second consequence of this statement is that the left hand side of the assignment operator should always be a field name. You cannot assign values larger than 0 and smaller than 1000 to the sum of all threshold_value values...

                   

                  Best,

                   

                  Peter