Data aggregation issue in calculated field

Question & Answer
,
1

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:

PIe Order Example

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)

2

Hi @AlexGW ,

Welcome to the QuickSight Community!

Here is the modified dashboard in the Arena. Please check out.
sample output 36264

Since the sample visual calculates the total quantity at item level, I have used item as partition to calculate quantity and used the same to determine the price. If your use case demands the calculation at a different level, please tweak the calculated field of quantity.

Thanks,
Prantika

2 Likes
3

Hi Prantika, Many thanks for this!

I had created a ‘Calculated Quantity’ field using sumOver, but was missing the PRE_AGG arguement as in your example.

Using the calc field with this arguement has fixed my aggregation errors. :slight_smile:

2 Likes