Calculating dates

[canerkan]

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!

[dplr-rn]

Please explain a bit more
- User only selects a Year not a date i am assuming
- you need set analysis
- something like {$<your_date={">=$(=Date(vStart))<=$(=Date(vEnd))"} >}
- where vstart and vend are variables defining start of year and end
- not knowing your datamodel hard to help with specific formula
with this you can do conditions on begin and end dates

[canerkan]

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.

[canerkan]

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.