Dynamic Price Ranges

Thu, 31 May 2012 18:35:45 GMT

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 1	P_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 11	11 to 12	12 to 13	13 to 14	14 to 15	15 to 16	16 to 17	17 to 18	18 to 19	19 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

P_Group 1

P_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 11

11 to 12

12 to 13

13 to 14

14 to 15

15 to 16

16 to 17

17 to 18

18 to 19

19 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.

topic Dynamic Price Ranges in QlikView

Dynamic Price Ranges