New to QS - Aggregating inside Calculated Fields for KPI

Hello, I’m new to QuickSight but am familiar with building out calculated formulas in other tools like Power BI. I’m having the hardest time trying to figure out how to build a simple KPI and I’ve scoured the internet and the very few forums I’m actually seeing for QuickSight but I’m obviously not understanding something and still cant figure it out.

I’m working with transactional sales data and in simple terms all I am trying to do is get the total quantity for each item in a certain period and multiply that by the unit price difference across each item and return the total amount. Basically, I am looking at calculating savings overtime in this scenario where item pricing has changed on the parameter ‘NewContractStartDate’. Below are progress notes and the current formula I have so far. Any help is much appreciated!

** Need to get to the KPI level SUM, Not providing a total in the table visual either yet *
** Using a minOver() with PRE_AGG for the unit prices resulted in row-level & total zeros*

// TOTAL NEW QTY OVER EACH ITEM
sumOver(ifelse({date} >= ${NewContractStartDate}, {qty},0), [{item}])
// TIMES
*
// UNIT PRICE DIFFERENCE AMT
// IF…
ifelse(
//NEW UNIT PRICE
minIf(
minOver(unit_price,[{item}], PRE_AGG).
date >= parseDate(‘2022-11-01’)
)
// LESS THAN
<
//BASE UNIT PRICE
minIf(
minOver(unit_price,[{item}], PRE_AGG),
date >= parseDate(‘2021-11-01’) AND date < ${NewContractStartDate}
),
// THEN…
//BASE UNIT PRICE
minIf(
minOver(unit_price,[{item}], PRE_AGG),
date >= parseDate(‘2021-11-01’) AND date < ${NewContractStartDate}
)
// MINUS
-
// NEW UNIT PRICE
minIf(
minOver(unit_price,[{item}], PRE_AGG),
date >= parseDate(‘2022-11-01’))
//ELSE
, 0 )

Hi @TawTaw,

What do you mean by KPI level SUM? Do you mean that you want to add the result of your calculation across all items in your dataset?

What’s the granularity of your dataset? Is each row in your dataset one item?

Hi David,

You are correct, I’m looking to build a KPI that will provide the total sum across all items in the dataset. The granularity is transaction level, so there could be multiple rows of the same item on the same day purchased at different quantities and I’m looking to get the savings for each item (the price difference between the min unit price prior to a given date and the min unit price after a given date). Ideally I would be able to show this in a table visual (for a look at the aggregated savings for each item) as well as a total, single value KPI to show total savings. But maybe I’m missing something and these have to be broken out into different calculated fields? Does that clarify? Please let me know if more detail is needed.

Thank you for any guidance you may have for me!

Is there a particular reason why you didn’t use PRE_AGG here but you used it in minOver? When the calculation level isn’t specified, POST_AGG is implied.

Did you get the savings per item that you wanted? Assuming your item level savings is correct, what happens if you put sum({savings per item}) in a table or KPI visual? Does it give you the correct result?

1 Like
  • PRE_AGG: Yes, using PRE_AGG on that sumOver line caused an error " For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated. " and I was unable to save and test the calculated field.

  • SUM FIELD: Right now it is showing 0s across all rows of a table visual by item, but if I remove the minOver() and just keep those portions as minIf(unit_price) (no PRE_AGG) then I at least get the row level detail that is correct, but no total. And when I create another field to sum this one, i get this nested aggregate error " Nesting of aggregate functions like {{aggregateFunction1}} and {{aggregateFunction2}} is not allowed."

Thank you for helping look into this!

I’ve made some progress I think. With the below I am able to now get accurate row-level sums aggregated by item as well as a total sum amount in a table visual to show savings by item. However I am still unable to get this to show as a single value in a KPI visual. When I try to add this field to a KPI visual I get the error “Table calculation attribute reference(s) are missing in field wells”. Which is odd since I know you can add only one field to a KPI visual and it will work properly, no need to add additional fields and even if I do add a target field or group field, nothing changes, it still shows the same error.

Below is the entire formula I’ve written (Ideally the repeated portions would be a reference to another calculated field but I broke it out here to show the complete detail with comments). Hopefully this helps someone help me :slight_smile: I’m desperate to find a solution, any guidance is very much appreciated!

sumOver(
    ifelse(
        // new qty
        (sumIf(
        quantity,
        date >= parseDate('2022-11-01') 
        )
        *
        // unit savings amount
        (ifelse(
            //New Unit Price
            minIf(
            unitprice,
            date >= parseDate('2022-11-01')
            )
            < // is less than 
            // Old Unit Price
            minIf(
            unitprice,
            date >= parseDate('2021-11-01') AND date < parseDate('2022-11-01')
            )
            ,// THEN
            // Old Unit Price
            minIf(
            unitprice,
            date >= parseDate('2021-11-01') AND date < parseDate('2022-11-01')
            )
            - //MINUS
            //New Unit Price
            minIf(
            unitprice,
            date >= parseDate('2022-11-01')
            )
            , //ELSE
            0 )))
        > 0// is greater than 0
        ,// THEN
        (sumIf(
        quantity,
        date >= parseDate('2022-11-01') 
        )
        *
        // unit savings amount
        (ifelse(
            //New Unit Price
            minIf(
            unitprice,
            date >= parseDate('2022-11-01')
            )
            < // is less than 
            // Old Unit Price
            minIf(
            unitprice,
            date >= parseDate('2021-11-01') AND date < parseDate('2022-11-01')
            )
            ,// THEN
            // Old Unit Price
            minIf(
            unitprice,
            date >= parseDate('2021-11-01') AND date < parseDate('2022-11-01')
            )
            - //MINUS
            //New Unit Price
            minIf(
            unitprice,
            date >= parseDate('2022-11-01')
            )
            , //ELSE
            0 )))
        , // ELSE
        0)
,[{item}])

When you use sumOver without specifying the calculation level, POST_AGG is implied. When you use POST_AGG, all the fields used in your calculated field need to be in your visual which isn’t possible in your case.

PRE_AGG: Yes, using PRE_AGG on that sumOver line caused an error " For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated. " and I was unable to save and test the calculated field.

This is basically saying you can’t have an aggregation like sum, min, max, etc inside of sumOver when you use PRE_FILTER and PRE_AGG. Is your qty field calculated? If so, does it use one of those aggregations?

1 Like

Since you were able to get your item level savings, I think you should be above to just wrap it in a sum. Can you try sum instead of sumOver?

1 Like

Hey David,

Really appreciate all your help here. I tried using SUM() to wrap the entire code in place of SumOver() and I get a ‘nesting of aggregate functions’ error.

To answer your other question on the quantity field, that one is a base field (row-level transactions), no other calculations on it. Should I be adding another calculation to it to help here?

Hi @TawTaw,

I split your calculation into several smaller calculated fields to better understand it.

This is what you’re doing right now:

New qty =
sumIf(quantity, date >= parseDate(‘2022-11-01’)

New unit price =
minIf(unitprice, date >= parseDate(‘2022-11-01’))

Old unit price =
minIf(unitprice, date >= parseDate(‘2021-11-01’) AND date < parseDate(‘2022-11-01’))

Unit savings amount =
ifelse(
{New unit price} < {Old unit price},
{Old unit price} - {New unit price},
0
)

Item savings amount =
ifelse(
{new qty} * {unit savings amount} > 0,
{new qty} * {unit savings amount},
0
}

Total savings amount =
sumOver({Item savings amount}, [{item}])

Can you try this approach instead?

New unit price =
minOver(
	ifelse(
		date >= parseDate('2022-11-01'),
		unitprice,
		null
	),
	[{item}],
	PRE_AGG
)
 
Old unit price =
minOver(
	ifelse(
		unitprice, date >= parseDate('2021-11-01') AND date < parseDate('2022-11-01'),
		unitprice,
		null
	),
	[{item}],
	PRE_AGG
)

Unit savings amount = 
 ifelse(
	{New unit price} < {Old unit price},
	{Old unit price} - {New unit price},
	0
)
	
Item savings amount = 	
ifelse(
	{new qty} * {unit savings amount} > 0,
	{new qty} * {unit savings amount}, 
	0
}
	
Total savings amount = 
sum({Item savings amount})
1 Like

Hey David,

This is very helpful, thank you. Apologies if I misunderstood your earlier question on the quantity field. While the base ‘quantity’ field is not a calculated field, the ‘new qty’ field is a calculation:

// New Qty
sumIf( qty, date >= parseDate('2022-11-01'))

Everything you recommended I was able to create and it worked great up until the Item savings amount formula where maybe my above ‘new qty’ calculation might be the issue here? Because I get a error: ** “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.” **

I tried removing that calculation and just reverting to the base ‘quantity’ field to confirm there would be no errors and it would provide an output to a KPI and it does however since that date filter has been removed its not accurate. But I’m not sure how to adjust that calculation to get around that error and also keep the date… Do you know a trick there by chance?

Again, thank you so much for all of your help!

Sorry, I meant to change the calculation for New Qty too. Can you change it to this?

New qty = 
sumOver(
	ifelse(
		date >= parseDate('2022-11-01'),
		quantity,
		null
	),
	[{item}],
	PRE_AGG
)
2 Likes

Thanks for confirming. I ended up adjusting the new_qty field slightly to:

ifelse(date >= ${ContractStartDate}.
          quantity, NULL)

And it now works!!! At the row level and the KPI visual value now. :smiley: Thank you so much David for all of your help!!! :raised_hands:

1 Like

Glad I could help! It’s been a while since I used Power BI but I think using sumOver in QuickSight is like creating a calculated column in Power BI and using sum is like creating a measure.

You can’t use sum( ) inside sumOver( ) but you can use sumOver( ) inside sum( ). You also can’t do things like sumOver( ) - sum ( ) or sumOver( ) * sum( ). That’s what that mismatched aggregation error means.

2 Likes