Calculated fields: Level Aware Calculations

[koflote]

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:

1. max(numerator) grouped by months and Vendor for last year
   2)sum(denominator) for last year
2. numerator/denominator
   I have done this calculation before for a simple sum of numerator and sum of denominator then i divide the numerator and denominator, but since i am trying to get the max of numerator and sum of denominator before dividing i got wrong figure. e.g
   Last year numerator: sumIf(numerator,{end_of_month}>={Last Year} AND {end_of_month}<={Current Year})
   Last year denominator: sumIf(denominator,{end_of_month}>={Last Year} AND {end_of_month}<={Current Year})
   Full year last year(final calculation): {FY’LY Numerator}/{FY’LY Denominator}
   How do i replicate this for my use case?

[ErikG]

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

Maybe you can have a look at:

Previous Year YTD Aggregation functions

Hi ChenjingLyu, I hope below solutions will meet your requirements, kindly check these calculations Max Date: maxOver({your date field}, [ ], PRE_AGG) Between dateDiff(), addDateTime(), truncDate(), and extract() you can define whatever periods you want (and there are often multiple ways to do each one, these are just some proven examples). Please create Parameter ( Name it as AsOfDate ) by selecting fixed date option, then try below calculations Today: ifelse(dateDiff({order_date},${As…

BR

[ErikG]

Hi @koflote
could the other topic help?
BR

[koflote]

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:

1. sum(numerator)
2. sum the max(denominator by vendor code and month) denominator at month and vendor code level
3. numerator/ denominator.

From the topic you referred i couldn’t achieve that.

[duncan]

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}, [{vendor_code}, {Month}]), {end_of_month}>={Last Year} AND {end_of_month}<={Current Year})

calc3: cal1/calc2

I have a feeling it may throw errors for calc2 but let me know what you get. Let me know if this is closer to what you are looking for!

[koflote]

Hi @duncan, 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

[David_Wong]

@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]
	)
)

[koflote]

Hi @David_Wong This worked!. Thank you.

[David_Wong]

@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.

[koflote]

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