Hi guys, I am trying to calculate last year for my metric in Quicksight calculated field but i keep having inaccurate figures. What i am trying to achieve is: max(numerator) grouped by months and Vendor for last year 2)sum(denominator) for last year numerator/denominator I have done this calcula…

@koflote Try this for your second calculated field: sum( max( ifelse( {end_of_month}>={Last Year} AND {end_of_month}<={Current Year}, Denominator, null ), [{Vendor Code}, Year, Month] ) )

Hi @koflote what kind of “inaccurate figures” do you get? Maybe you can have a look at: [image] Previous Year YTD Aggregation functions Question & Answer Hi ChenjingLyu, I hope below solutions will meet your requirements, kindly check these calculations Max Date: max…

Hi @koflote could the other topic help? BR

Hi @ErikG thanks, the other topic was helpful for another problem I was facing, but did not work for this. I tried to map out what i want to achieve in the excel. In the sample data i did in excel, i am trying to get the defect % total. Steps: sum(numerator) sum the max(denominator by vendor code …

Hello @koflote and @ErikG ! @koflote were you able to resolve this or are you still running into this issue? Let me know if this throws any errors but could you try the following: calc1: sumIf(numerator,{end_of_month}>={Last Year} AND {end_of_month}<={Current Year}) calc2: sumif(max({denominator…

Hi @WLS-D , you are correct about the Calc2, i have tried it before and it gives the error ‘Mismatched Aggregation’. That the issue i am still facing now [image]

Hi @David_Wong This worked!. Thank you.

@koflote The reason why max inside a sumIf doesn’t work is because your condition is evaluated at the row level but the thing that you’re summing is an aggregated value obtained by grouping multiple rows.

Oh yeah, makes a lot of sense. Thank you for the breakdown.

Calculated fields: Level Aware Calculations

Q&A

ErikG January 25, 2024, 11:27am 2

Hi @koflote
what kind of “inaccurate figures” do you get?

Maybe you can have a look at:

Topic		Replies	Views
How to compare the last date with other dates in Quick Sight Q&A error , quick-sight , calculations , Business-Intelligence-Engineer	7	685	December 15, 2023
Previous Year Total values (at Quarterly Level) in Quicksight Q&A error , quick-sight , calculations	10	1625	April 29, 2024
Get last year value (periodOverPeriodLastValue) in a PRE_AGG scope Q&A formulas , feature-request , quick-sight , calculations	5	1297	May 10, 2023
LAC with If else Q&A filters , date , quick-sight , calculations , Business-Intelligence-Engineer	9	90	July 25, 2024
Aggregate base on the condition Q&A calculations , Business-Intelligence-Engineer	3	40	January 7, 2026

Calculated fields: Level Aware Calculations

Related topics