Get Free Trial

Complex calculations

Answered
Mohamed asked on September 22, 2021

Hi 
I need to do a custom calculation
there are 3 levels of header 

  1. category census & rest of the staff 
  2. shift end 
  3. staff vs required  

custom calculation formula => (all midnight total x 24)/ all midnight census
how can I achieve this ?
also for census category we don’t need 3rd level of header mentioned above, how can we remove that from the table?
Please find the sample data below,

{
"Facility": "ABCD",
"UnitType": "Unit a",
"Unit": "5NH",
"Month": 8,
"DayOfMonth": 14,
"ShiftEnd": "7am",
"StaffRequired": 3.0,
"StaffFirstName": "Tom",
"StaffLastInitial": "A",
"EncounterKey": null,
"RoomBedKey": null,
"PatientInitial": null,
"Category": "R Staffed"
},
{
"Facility": "ABCD",
"UnitType": "Unit b",
"Unit": "3NS",
"Month": 8,
"DayOfMonth": 14,
"ShiftEnd": "7am",
"StaffRequired": null,
"StaffFirstName": null,
"StaffLastInitial": null,
"EncounterKey": "****",
"RoomBedKey": "***",
"PatientInitial": "VC",
"Category": "Census"
},

Attachments:
whppd.PNG

2 answers

Public
Nadia Khodakivska Nadia Khodakivska Flexmonster September 27, 2021

Hello, Mohamed, 
 
Thank you for posting a question to our forum.
 
Firstly, we suggest pre-processing the data before loading it into Flexmonster. Feel free to check the solution on JSFiddle: https://jsfiddle.net/flexmonster/82eso1vm/
In the example (line 57 in JavaScript block) we separated “StaffRequired” to three columns “StaffRequired_7am”, “StaffRequired_7pm” and “StaffRequired_midnight”. In such a case “StaffRequired_midnight” can be used as a part of the custom formula To reach all midnight total in formula we used workaround from the following thread: https://www.flexmonster.com/question/using-sub-totals-in-a-calculated-measure/
To remove 3rd level of header you can edit Slice object to show only necessary data on your report. 
 
Please let us know if such an approach would work for your case. Looking forward to your response.
 
Kind regards,
Nadia

Public
Nadia Khodakivska Nadia Khodakivska Flexmonster October 4, 2021

Hello, Mohamed,
 
We are wondering if you had some time to test the solution.
Looking forward to hearing from you.
 
Kind regards,
Nadia

Please login or Register to Submit Answer