Changing Custom Aggregations

Q&A
, , , ,
1

Hi I’m wondering if there is a way to alter this Calculated Field so that it is not a Custom Aggregate?

Missing Hours:

ifelse(
sum({Timesheet Hours Current Month}) < avg({Expected Hours}),
1,
0
)

I’m trying to combine this with another aggregated field, but getting this error:

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

ifelse(
{EndOfMonthFlag} = 1 AND {Missing Hours} = 1,
1,
0
)

I need to combine these into one value so I can set a Threshold Alert, from testing I also don’t think it is possible to set an alert on a custom aggregate.

I have been trying to use sumOver to resolve this but not getting much joy.

2

Hi @Jabez ,

Can you share more details about the dimension against which you want to aggregate timesheet hours current month?

You may need to use the same dimension to replace sum with sumOver partitioned by dimension and avg with avgOver partitioned by dimension.

If this does not work, I would request you to share sample data/analysis in Arena for quick check.

Thanks,
Prantika

3

Hi @prantika_sinha, thank you for coming back to me on this.

I have tried your suggestion, but this leaves me with a Custom Aggregate and the same predicament.

I have managed to combine the two, by changing the EndOfMonthFlag from an Average to Custom Aggregate, allowing me to combine this with the Missing Hours (Custom Aggregate):

image
image1461×612 28.5 KB

This still leaves me with the problem that a Threshold Alert cannot be setup on a Custom Aggregate field.

image
image472×640 23.8 KB

I think I need to take the opposite approach and make Missing Hours an Average Aggregate,
So I can use this with End Of Month Flag, to create an Average Aggregated Combined Flag, which will allow a Threshold Alert. However, I can’t seem to find a way to do this.

Combined Flag - Missing Hours & End Of Month (Custom Aggregate)

ifelse(
{End Of Month Check Avg} > 0
AND
{Missing Hours} > 0,
1,
0
)

Missing Hours (Custom Aggregate)

ifelse(
sum({Timesheet Hours Current Month}) < avg({Expected Hours}),
1,
0
)

Expected Hours (Average Aggregate- Must be Average to work)

netWorkDays(truncDate(‘MM’, ${DateToday}), ${DateToday}) * 7.5

Timesheet Hours Current Month (Sum Aggregate)

ifelse(
substring({timesheet_dated_on}, 1, 7) = substring(toString(${DateToday}), 1, 7)
AND substring({timesheet_dated_on}, 9, 2) <= substring(toString(${DateToday}), 9, 2),
parseDecimal({timesheet_hours}),
0
)

Is there another way I can create a Threshold Alert using a Custom Aggregate field? This restriction seems unnecessarily obstructive.

4

Hi @Jabez ,

Can you mask and share sample data in Arena to understand this better?

I understand the issue you called out, but trying out the solution on sample data would help me to suggest accurate solution since the calculations here are level aware.

Thanks,
Prantika

5

Hi @Jabez,
It’s been awhile since we last heard from you, were you able to find a work around for your solution or did you have any additional questions?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

6

Hi @Jabez,
Since we haven’t heard back, I’ll go ahead and close this topic. However, if you still have additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!