Hello,

I have a dataset with subscription based orders. The unit price of items varies with quantity ordered. Sometimes a customer will make expansion purchases of said item during the subscription term.

I have built a calculated field based on new item cost, which sets an item price per bucket ordered.

What i’m trying to do now is create a new table for the ‘renewal’. This should aggregate the total quantity ordered over the term, and then have the calculated field to show the correct unit price per quantity bucket (counting up all items over previous orders), and then multiply that by the total quantity ordered to get the new renewal price.

I have tried various methods but have issues with nested aggregation, or mis-matched aggregation, and am a little stuck.

I have created a simplified version of the issue here:

The top table shows the orders customer has made over the term, and the list price per item when ordered.

The bottom table shows the total quantity ordered, new pie list price, and new quote price calculation. I would like this table to display the following:

|Item = Fruit Pie Subscription

|Quantity = 480

|New Pie List Prices = 3.2

|New Quote Price = 1536

I cannot get the fields ‘New Pie List Prices’ and ‘New Quote Price’ to display correctly however.

Any pointers would be greatly appreciated.

Thank you!

UPDATED:

I’m trying to use the IN function in my calculated field to include more than one item number, and then want to sum the invoiced quantity, but am having aggregation issues. The calculation that is not working is similar to this:

ifelse(

in({item_number}, [‘Fruit Pie 1’, ‘Fruit Pie 2’]) AND sum({invoiced_quantity}) >= 0 AND sum({invoiced_quantity}) <= 4, 45.00,

in({item_number}, [‘Fruit Pie 1’, ‘Fruit Pie 2’]) AND sum({invoiced_quantity}) >= 5 AND sum({invoiced_quantity}) <= 9, 42.13,

in({item_number}, [‘Fruit Pie 1’, ‘Fruit Pie 2’]) AND sum({invoiced_quantity}) >= 10 AND sum({invoiced_quantity}) <= 29, 38.10,

in({item_number}, [‘Fruit Pie 1’, ‘Fruit Pie 2’]) AND sum({invoiced_quantity}) >= 30 AND sum({invoiced_quantity}) <= 49, 35.78,

in({item_number}, [‘Fruit Pie 1’, ‘Fruit Pie 2’]) AND sum({invoiced_quantity})>= 50 AND sum({invoiced_quantity}) <= 69, 31.46,

in({item_number}, [‘Fruit Pie 1’, ‘Fruit Pie 2’]) AND sum({invoiced_quantity}) >= 70 AND sum({invoiced_quantity}) <= 99, 28.85,

in({item_number}, [‘Fruit Pie 1’, ‘Fruit Pie 2’]) AND sum({invoiced_quantity}) >= 100 AND sum({invoiced_quantity}) <= 149, 25.60,

in({item_number}, [‘Fruit Pie 1’, ‘Fruit Pie 2’]) AND sum({invoiced_quantity}) >= 150 AND sum({invoiced_quantity}) <= 199, 21.70,

in({item_number}, [‘Fruit Pie 1’, ‘Fruit Pie 2’]) AND sum({invoiced_quantity})>= 200, 20.80,

NULL)