Calculating Average Error in set modifier expression

[canerkan]

Hi everyone!

I am trying to calculate the average age of our employees at a selected date. I've already got a formula for this but somehow It gives me an error in set modifier expression. Can anynone help me finding out where this formula is wrong? This formula works without the set modifier expression.

avg({$<GetFieldSelections(Date)<{[End.autoCalendar.Date]},GetFieldSelections(Date)>{[Begin.autoCalendar.Date]}>}Age(GetFieldSelections(Date),[Birth date]))

GetFieldSelections(Date)<{[End.autoCalendar.Date]} = Disregarding the employees that left the company before the selected date

GetFieldSelections(Date)>{[Begin.autoCalendar.Date] = Disregarding the employees that joined the company after the selected date

[Anil_Babu_Samineni]

What if you selected two from Date field? Condition not at all correct to me.

[canerkan]

Hi Loveisfail,

thanks for your reply!

Selecting two dates wouldn't work.. The user will always just select one date. It's like a snapshot of the average age at this specififc date.

Do you know where I need to correct my formula?

[Anil_Babu_Samineni]

Good then? try this?

avg(If(Max(TOTAL Date)<[End.autoCalendar.Date] and  Max(TOTAL Date)>[Begin.autoCalendar.Date] , Age(Date(Max(Date)),Date([Birth date])))

I wonder, why you want to calculate average for Age? BTW, You can use above one. Else, First check this part?

Avg(Age(Date(Max(Date)),Date([Birth date])))

[canerkan]

Hi Loveisfail,

thank you, unfortunately it shows "Nested aggregation not allowed" with both formulas..

[canerkan · Accepted solution]

The right formula is

avg(Age(GetFieldSelections(Date),if(GetFieldSelections(Date)>Begin, if(IsNull(End)= True() or GetFieldSelections(Date)<End,[Birth date]))))

Took me a lot of time but I finally got it.