User-friendly way to override a formula in a line item allowing to edit the cell
Hi everyone,
I was wondering if there was a way to make it possible to edit the values of a line item that has a formula.
The most obvious way and that is taught in the Model Builder training is to do an override. To do that you create 3 line items:
- One with the value you want to override with.
- A boolean that indicates which of the values to consider.
- And, finally, another that will display the "final value" - which if the boolean is false is equal to the value of the line item that contains the formula, and if it is true is equal to the value of the new value entered.
However, on the dashboard this solution is not very user friendly so I would like to look for another one that would simply allow the user to change the values in the table presented to him or her.
My model consists of 3 modules and the information flow is as follows:
- Input --> SYS00 --> SYS01
The purpose of the Input model is to allow users to enter new employees or sets of employees (if the number is greater than 1) into the system. It looks like this on the UX page.
The user can fill in more than one line and when the user decides to "save" it runs an action that will add the new employees to a list and import the values entered in the Input to the SYS00. The SYS00 module can be seen in the following image.
The SYS01 module (next figure) allows a view by employee by month, which is the final desired look in the UX page. It displays for each employee (grouping them hierarchically) the "Number" starting from the "Month" previously entered through a formula [IF 'SYS00 Data'.Month <= ITEM(Time) THEN 'SYS00 Data'.Number ELSE 0].
The objective was to allow the user to modify the values of this table, but it is not possible because this line item contains the presented formula.
- I tried to create another module that imports through an action the values from this module and in that case it is then possible to edit the values, but when a new employee is added this module imports again the values from SYS01 eliminating the changes made.
- I also tried to change the "Formula Scope" option in the blueprint view from "All Versions" to "Actual Version" or "Current Version" and so it is possible to edit the line item however the formula functionality stops working and when a new employee is added the months appear all with zeros because the formula is not being used.
Does anyone know how to do this or if this is even possible?
Thank you,
João Nuno Cardoso
Answers
-
Hi @jncardoso
Thanks for such a detailed explanation of the problem statement, found it really intriguing.
One way I can think of solving this is to use incremental import and not do a full import always. Below is the outline of how you can do that:
- Input module for users - Similar to the one you have
- Add the line item "Input has changed?" - this one is to compare the inputs against the snapshot of inputs taken previousely
- Formula for the same: Headcount <> 'SNP01: Employee Details Snapshot'.Headcount OR Month <> 'SNP01: Employee Details Snapshot'.Month
- Snapshot module (SNP01) to capture user inputs - Similar structure as the input module
- Create a calculation module (SYS01) to calculate headcount
- This line item calculates differently based on whether the input has changed or not. If the input against an employee has changed, then the calculation uses the formula outlined by you, else picks up from the next module - OUT01 - which is an import from this SYS01 module
- Formula: <IF 'INP01: Employee Details Input'.Input has changed? THEN IF 'INP01: Employee Details Input'.Month <= ITEM(Time) THEN 'INP01: Employee Details Input'.Headcount ELSE 0 ELSE 'OUT01: Employee Details'.Headcount>
- Create the output module (OUT01) - this can be edited by users (no formula).
- Two Imports - one from SYS01 to OUT01 and the other from INP01 to SNP01 - put them in a process. Make sure to run the snapshot process at the end.
Running this process will not override any employee data for which the input has not been changed in the input module and thus preserves changes made in the OUT01 module.
Hope this helps, let me know how it goes.
1 - Input module for users - Similar to the one you have
-
Hi @jncardoso
Depending on the expected frequency of overwrites we can have the following solutions.
Case where the Overwrites are many:
Solution 1:
We can assume that entering an overwrite conveys an intent to overwrite so the Boolean check is redundant
In this case we can drive the final number with the logic (if overwrite >0 then overwrite else original value)
The ability to overwrite can be governed by using DCA control.
Case where Overwrites are rare:
Solution 2:
We can drive the overwrite by generating a transaction list of the overwrites using user input.
This transaction list item can have properties (number, employee, month) assigned by the user in a model which can be either pulled by formula (final value = if overwrite number in transaction module[sum employee, sum month] = 0 then original value else overwrite number in transaction module[sum employee, sum month])
Solution 3:
We can use the transaction module of Solution 2 to import values into overwrite value in Solution 1
Regards,
Manish
0 -
@jncardoso Your challenge here is based on conflicting business requirements.
- On the one hand, you've set up a process where the headcount forecast is based on real data - i.e. an aggregation of transactional rows.
- On the other hand, you have users that want to enter data that is NOT based on underlying transactional data.
In order to solve this, I think you need to determine the transactional data impact when a user enters a regular number in your summary table. Do you need there to be transactional data to support the number or not? You are going to have data mismatches with the structure you're proposing.
That said, if the transactional data is meant to be actuals data, and you want users to enter a forecast beyond a certain time frame, it does seem like Versions is the right solution to look at. Take a look at how Switchover works and see if that will meet the need. There is a 5-min micro-lesson about using switchover.
You can also read the Anapedia page on it:
1
