Calculated Amount

[KC3]

Hi Qlikians,

Please help me to get the formula for my Shipping cost.

As now I am applying just a simple formula for my order count multiply by 65 per order here it is. (Count(distinct [Order Number]))*65

But now i have to modify as per the Quantity level. As i have different quantity with different order.

If in my order there are only 1 Net Quantity then shipping will be 40 rs, if my Net Quantity for the orders is 2 then shipping will be 45, if my Net Quantity is 3 to 4 then shipping will be 50, if my Net Quantity is 5-7 then shipping will be 65, and last if my Net Quantity will be more than 7 then shipping cost per quantity will be 70.

Please help me to get the formula.

Please note getting the order count as per order numbers as there are different skus in one order number so order number will be repeated that's why i used distinct formula

[ogster1974]

To do it in the back end.

1. Create a shipping cost table with Quantity and cost.  You can expand later to include effective periods and taxes etc. (LOOKS LIKE YOUD HAVE 7 ROWS AT THE MOMENT)

Create a mapping table 

Mapping 

ShipCostMap:

Load

Qty,

Cost

Resident Costs

;

2. Then create a new order table in your model if you don't already have an order header record.

Orders:

load

*

,

ApplyMap('ShipCostMap', "total qty",70) as "Shipping Cost"

;

Load

OrderId,

Sum(qty) as "total qty"

Resident OrderDetail

Group by OrderId

;

[vinieme12]

please post some sample data with expected output