Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
What if you selected two from Date field? Condition not at all correct to me.
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?
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])))
Hi Loveisfail,
thank you, unfortunately it shows "Nested aggregation not allowed" with both formulas..
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.