Hello guys, I’ve been trying to make this calculation work unsuccessfully. I have the following calculated fields:
GMX FXN L28D = windowSum(sum({gmv_fxn}), [data ASC], 27,0)
GMV L28D Y-1 = windowSum(periodOverPeriodLastValue(sum(gmv), data, DAY, 364), [data ASC], 27,0)
I use them to calculate an YoY L28D Sum Growth rate like this (can’t use ‘periodOverPeriodPercentDifference’ because they aggregate two different fields):
FXN YoY L28D = ({GMX FXN L28D}-{GMV L28D Y-1})/{GMV L28D Y-1}
The problem is that I need to add these conditions to the calculation
{go_live_date} <= addDateTime(-12, ‘MM’, now())
{GMX FXN L28D}/{GMV L28D Y-1} < 9)
{business} = ‘B2C’)
This is what I’ve tried so far, but I still get the same ‘Mismatched Aggregation’ error in both options:
sumif({FXN YoY L28D}, {go_live} <= addDateTime(-12, ‘MM’, now())
AND({business} = ‘B2C’)
AND({GMX FXN L28D}/{GMV L28D Y-1} < 9)
ifelse({go_live} <= addDateTime(-12, ‘MM’, now())
OR({GMX FXN L28D}/{GMV L28D Y-1} > 9)
OR({business} <> ‘B2C’), NULL,
{FXN YoY L28D})
I’ve also tried to add those conditions as Filters in the Visual, but this part: ‘{GMX FXN L28D}/{GMV L28D Y-1} > 9)’ needs to consider the results aggregated by Customer Account, not only by Date as it’s doing right now. LAC-A and LAC-W don’t work in this case because they don’t support ‘windowsum’ and ‘periodoverperiod’ functions. Is there a way to solve this without having to create a new View or Table?