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 )
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.
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?
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."
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 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?
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?
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?
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.