Creating a Calculated Table in QlickView

[#1]

So what I have currently is a table with  3 fields: Dates (detailed dates spanning over 5 years), Sale (spanning over 0-3000), Quantity (spanning 0-40).

What I want is to create a table that has 5 fields.

Field 1: Year

Field 2: Sales Lower Bound

Field 3: Sales Upper Bound

Field 4: Sum of sales (within the bounds for that year)

Field 5: Sum of Quantity (within the bounds for that year)

Where the bounds are predetermined to be 0, 25, 50, 100, 150, 200, 300, 500, 1000, 2000, 3000.

So for example if I want the line it will look like this

Year = 2005

Sales Lower Bound = 0

Sales Upper Bound = 25

Sum of Sales = sum(if(Sale >= 0 and Sales < 25 and Year = 2005, Sale*Quantity, 0))

Sum of Quantity = sum(if(Sale >= 0 and Sales < 25 and Year = 2005, Quantity, 0))

I can create all (5x10=50) lines as separate 1 line tables and concatenate them after but there has to be a better way.

Any help

[oknotsen]

I would suggest to make an inline table in your script that contains the limits of those bounds.

Example:

Bounds:
LOAD * INLINE [
BoundName, BeginBound, EndBound
0-25, 0, 25
25-50, 25, 50
50-100, 50, 100
100-150, 100, 150
150+, 150
];

Make this list complete; I think you get the idea.

Next, do an interval match of this sales table with that bounds table.

Left JOIN (YourSalesTable)
IntervalMatch (SalesResults)
Load
BeginBound,
EndBound
Resident Bounds;

Now you have a Bounds dimension in your data model, you can use this as a dimension in your table to get the numbers you want.

[#3]

An interesting a good approach. Definite more elegant. Thank you.