Upload historical info not associated to master items

[ITMercon]

Hi everyone, I'm newbie in the whole Qlik Sense World and I need help.

I'm building an HR dashboard, my master items are all the information associated with employees, however, in my charts, I need to show the number of employees that existed in specific months. This information is a fixed value and hasn't been associated to this app.

There are several tables like this, I'm willing to compare per Years (14-15,15-16...) different variables> months, countries, are range, etc.

Master data:

Registers per Employee.

I don't have any idea how to relate both information, is there a way to create a measure for each crop year, or would it be better to create an association in the data manager? How can I bring into visualization information which is from historical fixed data?

Thanks in advance

[ArnadoSandoval]

Hi @ITMercon 

You should consider adding dummy records representing the missing years; If my interpretation of your first screenshot is accurate, you don't have data for the years 2014-2015 and 2015-2016, here you have two options, adding a single dummy record for the year 2014-2015 and another for the year 2015-2016; or adding dummy employees to the master data as many as required to fill those years; the single summary record is the one I had used.

Hope this helps,

[ITMercon]

Hey! thanks for the answer! My db and main table in the app is all employees who have existed in the company since 2014 (Actives and inactive employees).

We have a special year it goes from October to September that's why my periods are 2 dimensions:

Attrition crop year= According to attrition date, set year period

If([Attrition Date]<'10/01/2014','Before 14-15',If([Attrition Date]>='10/01/2014'and [Attrition Date]<='09/30/2015','14-15',If([Attrition Date]>='10/01/2015' and[Attrition Date]<='09/30/2016','15-16',If([Attrition Date]>='10/01/2016' and [[Attrition Date]<='09/30/2017','16-17',If([Attrition Date]>='10/01/2017'and [Attrition Date]<='09/30/2018','17-18',If([Attrition Date]>='10/01/2018'and [Attrition Date]<='09/30/2019','18-19',If([Attrition Date]>='10/01/2019'and[Attrition Date]<='09/30/2020','19-20',If([Attrition Date]>='10/01/2020' and [Attrition Date]<='09/30/2021','20-21'))))))))

Hire Crop year: According to Hire Date, Set Hiring year period

=If([Hire Date]<'10/01/2014','.Before 14-15',If([Hire Date]>='10/01/2014'and [Hire Date]<='09/30/2015','14-15',If([Hire Date]>='10/01/2015' and [Hire Date]<='09/30/2016','15-16',If([Hire Date]>='10/01/2016' and [Hire Date]<='09/30/2017','16-17',If([Hire Date]>='10/01/2017'and [Hire Date]<='09/30/2018','17-18',If([Hire Date]>='10/01/2018'and [Hire Date]<='09/30/2019','18-19',If([Hire Date]>='10/01/2019'and[Hire Date]<='09/30/2020','19-20','')))))))

So I use the 2 dates Attrition or Hiring to distribute the employees in different years periods. However, to calculate the total of people who were "actives" i don´t know how to articulate the information so it appears the same numbers as shown in the first table.