periodOverPeriodPercentDifference fails with Ifelse clause

Question & Answer
, , , , , ,
1

Hi Team -

I am trying to calculate PeriodOverPeriod i.e. weekly OR monthly OR quarterly (comes from filter).
Below logic works perfectly fine without ifelse clause but as soon as i try to add ifelse based on filter i.e. interval_type it fails with “”

Working logic (Without Ifelse)

concat(substring(toString(periodOverPeriodPercentDifference(max({p50_loadtime}), {start_date}, MONTH, 1)*100),1,5),'%/',substring(toString(periodOverPeriodPercentDifference(max({p90_loadtime}), {start_date}, MONTH, 1)*100),1,5),'%')

Failed logic (Post adding Ifelse)

ifelse({interval_type}='weekly',

concat(substring(toString(periodOverPeriodPercentDifference(max({p50_loadtime}), {start_date}, WEEK, 1)*100),1,5),'%/',substring(toString(periodOverPeriodPercentDifference(max({p90_loadtime}), {start_date}, WEEK, 1)*100),1,5),'%'),

{interval_type}='monthly',

concat(substring(toString(periodOverPeriodPercentDifference(max({p50_loadtime}), {start_date}, MONTH, 1)*100),1,5),'%/',substring(toString(periodOverPeriodPercentDifference(max({p90_loadtime}), {start_date}, MONTH, 1)*100),1,5),'%'),

concat(substring(toString(periodOverPeriodPercentDifference(max({p50_loadtime}), {start_date}, QUARTER, 1)*100),1,5),'%/',substring(toString(periodOverPeriodPercentDifference(max({p90_loadtime}), {start_date}, QUARTER, 1)*100),1,5),'%')
)

Error Message -

Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

2

Hello @bipshik, what if you used a parameter instead of a field? I’m assuming you have a control based on a filter for interval type that determines the values that should be returning. What if instead you made a parameter called intervalType and gave it a default, either weekly, monthly, or quarterly.

Then you can create a control and give it the 3 dropdown options. Then instead of checking for your interval_type field, you can replace it with the parameter ${intervalType}. Alternatively, you could also try creating individual calculated fields for each of the concat functions and replace the function with that field name in the ifelse. I will mark this as the solution for now, but let me know if you have any other issues!