Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone!
I am struggling very bad at the following calculations so I'd be very happy if someone could help me!
At first I am having difficulties in getting a filter panel which shows all Years from the MIN of the dates of my Table (column "Begin") until now. It has gaps and I don't know how to fill them..
The second is, I am trying to calculate the average age of our employees at the time of the selected year.
Like Sum(Selected Year e.g. 01.01.2018 - Birth date)/Total of employees at the time (= gives average age of employees at 01.01.2018).
The thing is, I have to consider the employees that have left our company at the time (column "End") before the selected year and the ones that joined our company after the selected year (probably IF-Function) and I don't know what function i have to use to get the selected year in my formula.
I have an Idea how to get the Total of employees but I really don't know how to get the selected year in my formula and how to fill the gaps of the years in my filter panel..
Somehow I am unable to upload a sample .qvf file, so please find attached my sample .xlsx
It would be very great if someone could help me!
Many thanks in advance!
Hi Dilipranjith,
thank you for your reply.
You are right, the user selects only a year if this is possible. I guess to calculate the avg age, the user will have to select a complete date like 01.01.2018 to subtract birth date from
I want to put my formula in a KPI visualization. I thought about something like
AVG({$<[Selected_Year]<{[End]}, [Selected_Year]>[Begin]>}[Selected_Year]-[Birht date]) --- never used this function so the syntax might be wrong
Selected_Year is the variable which has the value of the Year selected in the filter panel.
If you need other information please let me know and I'll provide them.
Hi again,
I was able to figure everything out by myself. The only thing I didn't get done was the following formula:
avg({$<GetFieldSelections([Date])<{[End.autoCalendar.Date]},GetFieldSelections([Date])>{[Begin.autoCalendar.Date]}>}Age(GetFieldSelections([Date]),[Birth date]))
It works without the set modifier expression (in Bold).. but i don't know where i did something wrong there.
I want to exclude every person who left the company before the the Selected Date (GetFieldSelections([Date])) and everyone who joined after the date as well.