Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

canerkan
New Contributor III

Calculating Average Error in set modifier expression

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

Labels (3)
1 Solution

Accepted Solutions
Highlighted
canerkan
New Contributor III

Re: Calculating Average Error in set modifier expression

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.

5 Replies

Re: Calculating Average Error in set modifier expression

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

 

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
canerkan
New Contributor III

Re: Calculating Average Error in set modifier expression

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?

Re: Calculating Average Error in set modifier expression

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])))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
canerkan
New Contributor III

Re: Calculating Average Error in set modifier expression

Hi Loveisfail,

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

Highlighted
canerkan
New Contributor III

Re: Calculating Average Error in set modifier expression

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.