0 Replies Latest reply: May 31, 2012 2:35 PM by Alfredo González RSS

    Dynamic Price Ranges

      Hello Everyone,

       

      I’ve been having trouble  related to a development in which we're looking to classify and sum the amount of kilograms of a product that were sold between certain price ranges (dynamic price ranges).

       

      Detailed explanation:

      There are different dimensions we'll be using to group the information, however for this example we could focuse on two: Group and Sub_Group. As you migh imagine each product is associated both to a Group and a Sub_Group.

      Each product can be sold at different price in every transaction and any amount of kilograms might be sold,therefore each Group and Sub_Group of products can have different price ranges.

      The total price range for a Group or Sub_Group is calculated as follows:  Max(Price) – Min(Price). Now that "spread" or delta is the total price range, we need to generate 10 equaly sized price ranges or groups to clasify the ammount to kilograms being sold in each Price Group.

       

      For example: For a specific Sub_Group the Min(Price) is 10 and Mac(Price) is 20. Then the total delta for this specific Sub_group is 10. So the 10 different Price Groups should be generated as follows:

       

       

      P_Group 1P_Group 2
      P_Group 3
      P_Group 4
      P_Group 5
      P_Group 6
      P_Group 7
      P_Group 8
      P_Group 9
      P_Group 10
      10 to 1111 to 1212 to 1313 to 1414 to 1515 to 1616 to 1717 to 1818 to 1919 to 20

      Sum of Kg. sold

      in this price range

      Sum of Kg. sold

      in this price range

      Sum of Kg. sold

      in this price range

      Sum of Kg. sold

      in this price range

      Sum of Kg. sold

      in this price range

      Sum of Kg. sold

      in this price range

      Sum of Kg. sold

      in this price range

      Sum of Kg. sold

      in this price range

      Sum of Kg. sold

      in this price range

      Sum of Kg. sold

      in this price range

       

       

      I tried to solve it using expressions like this one for each range of prices:

      sum({$<Price={'>=$(=(((max(Price)-min(Price))/10)*1)+min(Price))<$(=(((max(Price)-min(Price))/10)*2)+min(Price))'}>}Kilograms)

       

      The problem with this expression is that it doesn’t respect the maximumand minimum prices of each group or sub_group, it takes the maximum and minimum of all the universe of prices in the selections.

       

      I’ve tried to seek of a solution in the script but it doesn’t work as desired because it took away the dynamism (the user must be able to select different time periods) of the solution. So I believe that this has to be solved in the expressions itself.

       

      Hope someone has an idea that might help to solving this. I appreciate your time.

       

      Im also attaching an example .qvw that might clarify what I'm trying to do.

       

      Best regards.