HR Cube - Absenteism hours - Formulate
PYRAMID
Hello could someone help me on this one ?
I have a HR table, in which I have 3 columns :
- Absenteeism days
- Contract hours
- Calculation of absenteeism hours=absenteeism days*contract hours employee/31 = 2*60.67/31 = 3.91 absenteeism hours for blandin francois
Absenteeism days (1) * contract hours (2) / 31 days (number of days during the months)
This is the total of the table you can't see on the screen because it's too big :
You can notice in this establishment (IME BLAIN) there is only one person who is absent.
I would like to remove the column of the name and surname in order to have a table which give me establishment by establishment the absenteeism (hours).
Unfortunately as soon as I remove the column name and surname the data are not what am I expecting for :
The absenteeism hours is : 196.04 because pyramid calculate this way : 2 days * 3038.67 (hours contract of all the employees) /31 days in the month
Is there a ways that I can use formulate to make the table understand the absenteism is to calculate with the total hours contract employee BUT by employee and not the total ?
4 replies
-
Hi Nosikaa,
Is your HR table ingested / loaded into a database using a Pyramid Data Flow? Or is it Direct Query into an existing database of some type?
Calculations in formulate are what is known as "Semantic" calculations. That is, they are calculated in the context of the Pyramid Semantic model. By default, Pyramid models are aggregate in nature, i.e. when I look at Sales by Country, Pyramid sums all Sales to give me a total for each Country. Similarly, calculations are performed on these aggregated values. Thus, as you have seen in your example, if I'm looking at absenteeism hours, Pyramid will calculate that by aggregating days times aggregated hours divided by 31. If you are looking at the employee level, then this calculates correctly, but if you are looking at a higher level (by removing your columns), then the calculation is incorrect. For this calculation, it needs to be performed at the individual or record level, before aggregation.
If you have built the model by ingesting the data and processing through a data flow, then you can add a calculated column using the fields described, then simply use this field in your report. It will aggregate the correctly calculated values to give you the correct result at higher levels of aggregation.
If you have a direct query model, then the solution is a little more involved.
LMK if the above provides you with a solution, if not, we'll delve deeper!
Hope that helps.
Ian
-
Also, there is a new feature coming in Pyramid 2023 that will handle this scenario by setting the calculation to the underlying table rather than the semantic model. We're expecting to release a test version in the next week or so, it may be useful for you and your team to take look.
-
The Limited Test Program is now running. You can access the resources (software, guides, introductory webinar and slides) here:
https://www.dropbox.com/work/Pyramid%202023%20Limited%20Testing
-
Hi Nosika,
If you have a SSAS cube.
You cant use a script like this to calculate for each row with value on Absence column :
iif(isleaf([Personne].[Nom Complet].currentmember),
iif(isempty([Measures].[Jour absences]) or [Measures].[Jour absences]=0,null,[Measures].[Jour absences]*[Measures].[0030 Heures contrat]/31),
sum(Existing(Descendants([Personne].[Nom Complet].currentmember,,leaves)) ,
iif(isempty([Measures].[Jour absences]) or [Measures].[Jour absences]=0,null,[Measures].[Jour absences]*[Measures].[0030 Heures contrat]/31)))Copy / paste the script on New Formulte on Script Mode, and maybe adjust name of measures, dimensions and/or attributes.
I Hope that hepls you
Best regards
Laurent