Hello,
I have employee data in this format:
Employee | Department | Country | Date | Movement Type |
100 | IT | Germany | 31/01/2022 | Hire |
100 | IT | Germany | 28/02/2022 | Headcount |
100 | IT | Germany | 31/03/2022 | Promotion |
100 | IT | Germany | 30/04/2022 | Leaver |
From this information, I want to generate an analysis like:
Department | Promotion Rate | Hire Rate |
IT | 20% | 5% |
HR | 10% | 10% |
Commercial | 30% | 7% |
In the above dataset, I have only two dimensions: Department and Country.
However, in real data, I will have 20+ dimensions, and also will have trend data over multiple years.
I am wishing for a model which will provide a list of spikes (e.g. above or bellow X%) like:
1. In Oct 2021, there was a +7% spike in Hires, for Job Level = Vice President, Country = Netherlands.
2. In Sep 2020, there was a -3% spike in Promotions for Department = Commercial.
This means in the background, the model needs to run all the combination of Dimensions against all the Metrics I have throughout the timeline.
I am not expecting anyone to build the model for me, rather asking for advise with some direction.
Thanks.
Well, that's a good question...
I would create that "by hand" transposing the values and creating one analysis by one, using the multi-field formula... Perhaps there be a "easy" way to do that, but I don't know