Have you ever wanted to create an app with the analytics designer to plan for new projects and distribute the charges within the project timeframe? In this blog post you will learn how, and even more: steer the distribution with a factory calendar to have full control over the billable days.
A situation in which this could come in handy: Let's say you need to plan for an upcoming project spanning from September to October, for which you are going to charge your customers a certain amount. However, you need to make sure that you are not charging for weekends and public holidays, and charges occurring in September are written to the September account, while the charges for October goes into the October account.
Sounds interesting? Here is an example of how an app enabling this could look like (the project added in this example is within another timeline, but it is based on the same idea):
Feel like building something similar? Let us break this down step by step and I will help you through this – for this use case you will need to:
Start with a planning model. It will come with a default version and date dimension. Rename the date dimension to “FISCALPD”, for fiscal period. This dimension will enable an overview of the charges per period (month) later.
Add an account dimension and name it “MEASURE” to store the value of the charges and all date related measures.
Add the following members:
Next you will need a dimension for your factory calendar. This dimension should also help the system recognize the corresponding calendar month and year from a given date.
Add a new generic dimension, name it “WORKING_DAYS”. Add a new member for each day of the year 2020, and add the following properties:
As you need to be able to loop through the working days in the calculation later without having the unassigned member standing in the way, add the following property and just duplicate the dates.
For each day of the year, enter the corresponding properties. When you are done, you should have something like this (image is showing only first 10 rows):
Add now a level-based hierarchy and name it “YMD”, in the following order:
You should have something like this:
Add a new “PROJECT” dimension with the properties that you will need later:
Add a generic “CUSTOMER” dimension. Here you can maintain the list of existing customers who will be available for choice when you create a new project later. For this example, simply create CUST01, CUST02 and CUST03 as members.
Some holidays are location specific. Hence add a generic location dimension here to maintain the list of existing locations. For this example, we will just create the members LOC01 and LOC02.
You can use the newly created model to now set up the application and the factory calendar.
Create a new application with the Analytics Application Designer. Go to Menu > Create > Analytic Application
On the main canvas we shall create the factory calendar (it is only for calculation purpose and will be hidden later). Add a table based on your model, and filter in the following dimensions:
Make sure you check “Unbooked data” to make the table display all members for the Working Days dimension (each member represents a date).
For each date now enter 1 or 0 to mark it as a working or non-working day. You shall end up with something like this (image is showing only until 13th of January):
This serves as a reference for your data action to determine the billable days within the project time frame. Your data action will later loop through each date and check whether it lies within the defined time range; if true, it will write the value 1 or 0 to the member “IS_Billable_Day” of your “MEASURE” dimension.
Set the table up in the builder panel to be invisible in runtime - you do not need to display it constantly.
Now add two new tables. One for the overview of the total charges per project (created on the fly when the user adds a new project), and a second one for the overview of the allocated charges for each project per month (created via the allocation data action). They should look like this:
(You do not need to have the exact same layout, but it helps you to follow the flow of the data in this example. When you are done at the end and everything works, you are free to give this table a more user-friendly layout and title.)
Name the first table “Table_BookedOnly”. You can name the second table anyway you like.
For “Table BookedOnly” set up the visible properties for the PROJECT dimension.
And also apply the following filters:
Set here the width and height to “auto” in the styling panel for the table to extend with growing numbers of members.
Now you are done setting up the necessary environment for your use case. Next you will start building the UI for end user input and the data action.
Start by adding a new script object and call it “DateUtils”. Add the function “yyyymmdd”.
Set the return type to “string” and add “date” as argument.
This function should contain the following script to convert the date chosen from the date picker into the right format (yyyymmdd) which the system can process:
Next add another new script object and call it “MemberUtils”. Add a function “getProjectID” and set likewise the return type to string. You will need this to generate a new ID for each new project. Add the following script to this function:
You will need now to create a script variable for the project member that will be created on the fly by the end user:
Set it to return type “planningModelMember”.
Now you are done setting up the variables. It is time now to build the table that will hold the data. Move to the canvas for that. Click on it and create a table:
This new table is for creating the necessary layout so that you can write the new project and its properties into the model. This will involve showing all unbooked combinations, as they need to be in the layout to input ready. Make sure too that you have set up the relevant properties to be visible for the PROJECT dimension.
Your table will look like this (the properties will be shown later when there are booked values.)
Name it “Table_Planning” and set it up in the builder panel to be invisible in runtime - you do not need to display it constantly.
You can also freeze the table up to the column/row or row of your headers for better usability.
And set also here the width and height to “auto” in the styling panel for the table to extend with growing numbers of members.
Hide the table in design time so that it does not clutter your canvas.
Now you are done with the table, you are ready to create the UI for the end user to create new projects.
Add a new pop up and name it “Popup_CreateProject”. Set it up as follow in the builder panel:
Add now a panel to the popup to contain all the necessary input fields – the panel allows you to move the elements together as a group, which is easier while layouting. You can name the panel anything you like, as it will not be referenced anywhere else. Add the following fields to the panel and name them as shown:
Note: The date range picker is a custom widget. You can download it for testing purposes here:
https://widgets.nkappler.de/datepicker/
You need now to make sure that the “Create” button writes the new member into the project dimension, and the “Cancel” button closes the popup and cleans up any entries made. Hence click on fx of your “Popup_CreateProject” and add the following script:
And you are done with the UI for the end user! Now you just need a button to trigger this popup. Hence return now to your canvas (make sure your popup is no longer selected) and add a button. Name it “Button_NewProject”, and give it the text “+ New Project”.
Add the following script to its onClick event:
Now remember you have created two tables in the canvas? You may, if desired, add a button to toggle between them:
Add the following script to the onClick event:
This allows you to switch between the tables of booked and unbooked combinations. Your end user will not need it, but it is helpful for you while designing the app to check if the data is really written into the desired data slice.
For the last step of your app creation you will need to make sure that the dropdowns in the UI contains the existing customers and locations for choice. For that go to your canvas and add the following script to the onInitialization event:
Your UI is now up and ready! Run your app now and create a new project. Set it up this way:
Project name: Implementing Project
Project detail: Project Detail 1
Customer: CUST01
Location: LOC01
Start-/End-Date: 21.09.2020 to 05.10.2020
Total Billable (€): 12000
And you shall happily see the newly created project member in your table:
Do not forget to save your application!
Once the project is created and the total charges written into the model, we can start counting the number of billable days and allocating the charges across them. This is tackled by a data action with advanced formulas.
Go to Menu > Create > Process > Data Action
Give the data action a name and select the model you created for this use case. Save the data action.
Next, add the first advanced formula step for your data action. Call your first step “Clean”. You need this step to clean existing data in the chosen project, if any.
Switch to script mode and enter as the first line for the step:
This restricts the scope for the “PROJECT” dimension down to the chosen project. Below this line you will need to tell the system to delete any existing value for billables days, or previously defined ratio and allocated charges:
Now add a second advanced formulas step, and name it “Allocate”:
Switch to script mode and likewise here set up the chosen project as scope for the “PROJECT” dimension. Additionally, since you are planning for only a year, restrict the scope for working days to 2020.
Now for our calculation later we will need to know the proportion of the billable days in each month to the sum of billable days for the whole project, so we can allocate the right amount to the corresponding month. For that we will need to set up virtual members to store the aggregated values, which we can use in the calculation later:
Now here is the core of the script: You will need the data action to check against the factory calendar for billable days falling within the project time frame, and add them together later. Hence you will first create an if-statement to check for every date member of the WORKING_DAYS dimension whether it falls within the project timeframe:
For those dates fulfilling these conditions, you would like to mark them as billable days/fiscal period if the date is a working day according to 1 and 0 entries in the factory calendar.
Note:
Now you have successfully utilized the factory calendar to determine which the billable days/fiscal period. Next, you will need to calculate the aggregated billable days/project so as to determine the ratio for the allocation in a later step. For the aggregation, simply duplicate the above script, but this time make the system does not separate the billables days into respective fiscal periods – instead, write them all into the virtual members you have created, i.e. #TOTAL_DAYS and #TOTAL_MONTH:
Make sure you close your script:
Now let’s start defining how the allocation should run. We need first to define the ratio. In its simplest form the calculation looks like this:
Total billable days for the month/Total billable days for the entire project
Translating that into advanced formula:
You need to write that resulting ratio into the model. Hence add a DATA() command to the beginning and you will end up with this:
Now you just need to use the ratio to calculate the proportion of the total charges that should be allocated, by multiplying it with the total charges for the whole project:
And wow! You are done! You should have something that looks like this now for your second advanced formula step:
Save your data action. Add this to your application now, which you have created in step 2 of this use case.
Back to the application, triggering the data action now will prompt you to enter the desired project. Choose any existing project. Once you click ok, you can observe how the total charges are being allocated across the billable days in your monthly charges overview table.
And congratulations! You have just built a working planning tool for creating projects and allocating charges across defined fiscal periods!
If you have gotten a kick out of this just like I did, and plan to explore further potentials of the analytic designer or other data action functionalities, here are some other blog entries you might want to take a look at:
A situation in which this could come in handy: Let's say you need to plan for an upcoming project spanning from September to October, for which you are going to charge your customers a certain amount. However, you need to make sure that you are not charging for weekends and public holidays, and charges occurring in September are written to the September account, while the charges for October goes into the October account.
Sounds interesting? Here is an example of how an app enabling this could look like (the project added in this example is within another timeline, but it is based on the same idea):
Example of application
Dialog for entering new project
Feel like building something similar? Let us break this down step by step and I will help you through this – for this use case you will need to:
- Create the model
- Create the application in Analytics Designer and the factory calendar
- Set up a UI for entering new project with start and end date, location, customer and the amount to c...
- Use the factory calendar to determine billable days, and allocate the total charges to correct fisca...
[back to top]
STEP 1 - CREATE THE MODEL
Start with a planning model. It will come with a default version and date dimension. Rename the date dimension to “FISCALPD”, for fiscal period. This dimension will enable an overview of the charges per period (month) later.
CREATE DIMENSION: MEASURE
Add an account dimension and name it “MEASURE” to store the value of the charges and all date related measures.
Add the following members:
- IS_WORKING_DAY (for defining a certain date as a working day)
- IS_BILLABLE_DAY (to define billable days within the given time frame)
- RATIO (the percentage of the charges to be allocated)
- ALLOCATED_CHARGES
CREATE DIMENSION: WORKING_DAYS
Next you will need a dimension for your factory calendar. This dimension should also help the system recognize the corresponding calendar month and year from a given date.
Add a new generic dimension, name it “WORKING_DAYS”. Add a new member for each day of the year 2020, and add the following properties:
- CALMONTH (numeric, for calculation)
- MONTH (in written form, for display)
- YEAR
As you need to be able to loop through the working days in the calculation later without having the unassigned member standing in the way, add the following property and just duplicate the dates.
- DATE
For each day of the year, enter the corresponding properties. When you are done, you should have something like this (image is showing only first 10 rows):
Add now a level-based hierarchy and name it “YMD”, in the following order:
- Year
- Month
- Member ID
- Month
You should have something like this:
CREATE DIMENSION: PROJECT
Add a new “PROJECT” dimension with the properties that you will need later:
- START_DATE
- END_DATE
- PROJECT_DETAILS
- CUSTOMER
- LOCATION
- START_MONTH
CREATE DIMENSION: CUSTOMER
Add a generic “CUSTOMER” dimension. Here you can maintain the list of existing customers who will be available for choice when you create a new project later. For this example, simply create CUST01, CUST02 and CUST03 as members.
CREATE DIMENSION: LOCATION
Some holidays are location specific. Hence add a generic location dimension here to maintain the list of existing locations. For this example, we will just create the members LOC01 and LOC02.
[back to top]
STEP 2 - CREATE THE APPLICATION AND THE FACTORY CALENDAR
You can use the newly created model to now set up the application and the factory calendar.
Create a new application with the Analytics Application Designer. Go to Menu > Create > Analytic Application
On the main canvas we shall create the factory calendar (it is only for calculation purpose and will be hidden later). Add a table based on your model, and filter in the following dimensions:
- Working Days
- Version
- Fiscal Period
- Measure: Working Days
- Location
Make sure you check “Unbooked data” to make the table display all members for the Working Days dimension (each member represents a date).
For each date now enter 1 or 0 to mark it as a working or non-working day. You shall end up with something like this (image is showing only until 13th of January):
This serves as a reference for your data action to determine the billable days within the project time frame. Your data action will later loop through each date and check whether it lies within the defined time range; if true, it will write the value 1 or 0 to the member “IS_Billable_Day” of your “MEASURE” dimension.
Set the table up in the builder panel to be invisible in runtime - you do not need to display it constantly.
Now add two new tables. One for the overview of the total charges per project (created on the fly when the user adds a new project), and a second one for the overview of the allocated charges for each project per month (created via the allocation data action). They should look like this:
(You do not need to have the exact same layout, but it helps you to follow the flow of the data in this example. When you are done at the end and everything works, you are free to give this table a more user-friendly layout and title.)
Name the first table “Table_BookedOnly”. You can name the second table anyway you like.
For “Table BookedOnly” set up the visible properties for the PROJECT dimension.
And also apply the following filters:
- Customer: unassigned
- Fiscal Period: 2020
- Location: unassigned
- Measures: Total Charges
- Project: unassigned
- Version: public.Actual (Actual)
- Working Days: No Value (we will not be writing the charges to individual days, but allocate on a monthly granularity instead, and hence it is sufficient to just display the unassigned member, which will hold the total charges before the actual allocation.)
Set here the width and height to “auto” in the styling panel for the table to extend with growing numbers of members.
Now you are done setting up the necessary environment for your use case. Next you will start building the UI for end user input and the data action.
[back to top]
STEP 3 - SET UP A UI FOR ENTERING NEW PROJECT WITH START AND END DATE, CUSTOMER, AND THE AMOUNT TO CHARGE
Start by adding a new script object and call it “DateUtils”. Add the function “yyyymmdd”.
Set the return type to “string” and add “date” as argument.
This function should contain the following script to convert the date chosen from the date picker into the right format (yyyymmdd) which the system can process:
var x = date;
var y = x.getFullYear().toString();
var m = (x.getMonth() + 1).toString();
var d = x.getDate().toString();
(d.length === 1) && (d = '0' + d);
(m.length === 1) && (m = '0' + m);
var yyyymmdd = y + m + d;
return yyyymmdd;Next add another new script object and call it “MemberUtils”. Add a function “getProjectID” and set likewise the return type to string. You will need this to generate a new ID for each new project. Add the following script to this function:
var projectId = Project_and_Charges_Distribution.getMembers("PROJECT").length.toString();
(projectId.length === 1) && (projectId = '00' + projectId);
(projectId.length === 2) && (projectId = '0' + projectId);
return projectId;You will need now to create a script variable for the project member that will be created on the fly by the end user:
Set it to return type “planningModelMember”.
Now you are done setting up the variables. It is time now to build the table that will hold the data. Move to the canvas for that. Click on it and create a table:
This new table is for creating the necessary layout so that you can write the new project and its properties into the model. This will involve showing all unbooked combinations, as they need to be in the layout to input ready. Make sure too that you have set up the relevant properties to be visible for the PROJECT dimension.
Your table will look like this (the properties will be shown later when there are booked values.)
Name it “Table_Planning” and set it up in the builder panel to be invisible in runtime - you do not need to display it constantly.
You can also freeze the table up to the column/row or row of your headers for better usability.
And set also here the width and height to “auto” in the styling panel for the table to extend with growing numbers of members.
Hide the table in design time so that it does not clutter your canvas.
Now you are done with the table, you are ready to create the UI for the end user to create new projects.
Add a new pop up and name it “Popup_CreateProject”. Set it up as follow in the builder panel:
- Title: Create Project
- Buttons:
- Create: Emphasized, Enabled, Visible
- Cancel: Enabled, Visible
Add now a panel to the popup to contain all the necessary input fields – the panel allows you to move the elements together as a group, which is easier while layouting. You can name the panel anything you like, as it will not be referenced anywhere else. Add the following fields to the panel and name them as shown:
Note: The date range picker is a custom widget. You can download it for testing purposes here:
https://widgets.nkappler.de/datepicker/
You need now to make sure that the “Create” button writes the new member into the project dimension, and the “Cancel” button closes the popup and cleans up any entries made. Hence click on fx of your “Popup_CreateProject” and add the following script:
if (buttonId === "CREATE") {
Application.showBusyIndicator("Project " + project_Member.description + " (" + project_Member.id + ") being created...");
//write total billable into the model,and into the first month of the time frame.
var selection = {"@MeasureDimension":"[MEASURE].[parentId].&[TOTAL_CHARGES]",
"PROJECT" : project_Member.id,
"Version" : "public.Actual",
"FISCALPD" : "[FISCALPD].[YQM].&[" + DateUtils.yyyymmdd(DateRangePicker_StartEnd.getDateVal()).substring(0, 6) + "]",
"CUSTOMER" : Dropdown_Customer.getSelectedKey(),
"LOCATION" : Dropdown_Location.getSelectedKey(),
"WORKING_DAYS" : "[WORKING_DAYS].[YMD].&[#]"};
console.log(selection);
Table_Planning.getPlanning().setUserInput(selection,InputField_TotalCharges.getValue());
Application.setMessageTypesToShow([ApplicationMessageType.Error, ApplicationMessageType.Warning, ApplicationMessageType.Info]);
Table_Planning.getPlanning().submitData();
//get values for all properties
project_Member.description = InputField_Name.getValue();
project_Member.properties["START_DATE"] = DateUtils.yyyymmdd(DateRangePicker_StartEnd.getDateVal());
project_Member.properties["END_DATE"] = DateUtils.yyyymmdd(DateRangePicker_StartEnd.getSecondDateVal());
project_Member.properties["CUSTOMER"] = Dropdown_Customer.getSelectedKey();
project_Member.properties["PROJECT_DETAILS"] = TextArea_Details.getValue().substring(0, 127);
project_Member.properties["LOCATION"] = Dropdown_Location.getSelectedKey();
project_Member.properties["START_MONTH"] = DateUtils.yyyymmdd(DateRangePicker_StartEnd.getDateVal()).substring(0, 6);
console.log(project_Member);
//update the member and its properties. Refresh the tables.
Project_and_Charges_Distribution.updateMembers("PROJECT", project_Member);
Application.refreshData([Table_Planning.getDataSource(),]);
Application.setMessageTypesToShow([ApplicationMessageType.Error, ApplicationMessageType.Warning, ApplicationMessageType.Info, ApplicationMessageType.Success]);
Application.showMessage(ApplicationMessageType.Success, "Project " + project_Member.description + " (" + project_Member.id + ") successfully created.");
Application.refreshData([Table_Planning.getDataSource(), Table_BookedOnly.getDataSource()]);
this.close();
Application.hideBusyIndicator();
}
if (buttonId === "CANCEL") {
this.close();
Project_and_Charges_Distribution.deleteMembers("PROJECT", project_Member.id);
Application.refreshData([Table_Planning.getDataSource(), Table_BookedOnly.getDataSource()]);
}
Note*: Line 5-12, (var Selection😞 This is the vital part for writing the total charges (“total billables” in the end user UI) into the model. You will need to specify the structure of the data slice that should hold this information. Here you will also read the first 6 characters of the start date to get the start month of the project – an info which you will pass on to the data action later. Line 15, (setUserInput😞 writes the abovementioned data into the defined data slice Line 32, (updateMembers😞 Updates the new project member in the model with the new values for the properties. The actual creation of the new project member takes place somewhere else together with the triggering of UI, which will be touched on in section on “Button_NewProject”. Line 47 (deleteMembers😞 Removes the newly created project member upon explicit “cancel” triggered by the user. *(the source code block above does not display the code line number, but you can easily check it against the line number in your app once you have added the code in.) |
And you are done with the UI for the end user! Now you just need a button to trigger this popup. Hence return now to your canvas (make sure your popup is no longer selected) and add a button. Name it “Button_NewProject”, and give it the text “+ New Project”.
Add the following script to its onClick event:
//Create the variable “projectMember” and define that the new ID should be generated using the number of existing members in the PROEJCT dimension as input.
project_Member = cast(Type.PlanningModelMember, {
id: "PROJECT" + MemberUtils.getProjectID(),
description: "",
properties: {}
});
project_Member.properties["START_DATE"] = "";
project_Member.properties["END_DATE"] = "";
project_Member.properties["CUSTOMER"] = "";
project_Member.properties["PROJECT_DETAILS"] = "";
project_Member.properties["LOCATION"] = "";
project_Member.properties["START_MONTH"] = "";
//Display newly created ID in the UI
DisplayField_ID.applyText(project_Member.id);
Popup_CreateProject.open();
//Create new member
Project_and_Charges_Distribution.createMembers("PROJECT", project_Member);
Application.refreshData([Table_Planning.getDataSource()]);| Note: Line 19, (createMember😞 It is important here that a new project member is already created together with the triggering of the popup, so that when the user clicks “create” later on, the properties listed from line 8 to 13 are already up and ready for input. Upon “cancel”, this new project member will be removed. |
Now remember you have created two tables in the canvas? You may, if desired, add a button to toggle between them:
Add the following script to the onClick event:
Table_Planning.setVisible(!Table_Planning.isVisible());
if (Table_Planning.isVisible()) {
Button_BookedMode.setText("Show Booked Only");
} else {
Button_BookedMode.setText("Show Unbooked");
}This allows you to switch between the tables of booked and unbooked combinations. Your end user will not need it, but it is helpful for you while designing the app to check if the data is really written into the desired data slice.
For the last step of your app creation you will need to make sure that the dropdowns in the UI contains the existing customers and locations for choice. For that go to your canvas and add the following script to the onInitialization event:
var allCustomers = Project_and_Charges_Distribution.getMembers("CUSTOMER");
for (var i = 1; i < allCustomers.length; i++) {
Dropdown_Customer.addItem(allCustomers[i].id, allCustomers[i].id);
}
var allLocations = Project_and_Charges_Distribution.getMembers("LOCATION");
for (var f = 1; f < allLocations.length; f++) {
Dropdown_Location.addItem(allLocations[f].id, allLocations[f].id);
}| Note: You will not want the user to choose the unassigned member in both cases, hence i=1 and f=1, and not 0. |
Your UI is now up and ready! Run your app now and create a new project. Set it up this way:
Project name: Implementing Project
Project detail: Project Detail 1
Customer: CUST01
Location: LOC01
Start-/End-Date: 21.09.2020 to 05.10.2020
Total Billable (€): 12000
And you shall happily see the newly created project member in your table:
Do not forget to save your application!
[back to top]
STEP 4 - USE THE FACTORY CALENDAR, AND ALLOCATE CHARGES ACROSS BILLABLE DAYS
Once the project is created and the total charges written into the model, we can start counting the number of billable days and allocating the charges across them. This is tackled by a data action with advanced formulas.
Go to Menu > Create > Process > Data Action
Give the data action a name and select the model you created for this use case. Save the data action.
Next, add the first advanced formula step for your data action. Call your first step “Clean”. You need this step to clean existing data in the chosen project, if any.
Switch to script mode and enter as the first line for the step:
MEMBERSET [d/PROJECT]=%Project%| Note: Do not worry that the source code displayed is not in the format like how you probably know it in advanced formulas. It still works. |
This restricts the scope for the “PROJECT” dimension down to the chosen project. Below this line you will need to tell the system to delete any existing value for billables days, or previously defined ratio and allocated charges:
//Delete any existing data that might interfere with the calculation later
DELETE([d/MEASURE]="IS_BILLABLE_DAY")
DELETE([d/MEASURE]="ALLOCATED_CHARGES")
DELETE([d/MEASURE]="RATIO")Now add a second advanced formulas step, and name it “Allocate”:
Switch to script mode and likewise here set up the chosen project as scope for the “PROJECT” dimension. Additionally, since you are planning for only a year, restrict the scope for working days to 2020.
MEMBERSET [d/PROJECT]=%Project%
MEMBERSET[d/WORKING_DAYS].[p/YEAR] ="2020"Now for our calculation later we will need to know the proportion of the billable days in each month to the sum of billable days for the whole project, so we can allocate the right amount to the corresponding month. For that we will need to set up virtual members to store the aggregated values, which we can use in the calculation later:
VARIABLEMEMBER #TOTAL_DAYS OF [d/MEASURE]
VARIABLEMEMBER #TOTAL_MONTH OF [d/FISCALPD] Now here is the core of the script: You will need the data action to check against the factory calendar for billable days falling within the project time frame, and add them together later. Hence you will first create an if-statement to check for every date member of the WORKING_DAYS dimension whether it falls within the project timeframe:
FOREACH [d/PROJECT]
IF DATEDIFF( [d/PROJECT].[p/START_DATE] ,[d/WORKING_DAYS].[p/DATE] ,"DAY" )>=0 THEN
IF DATEDIFF([d/WORKING_DAYS].[p/DATE] ,[d/PROJECT].[p/END_DATE],"DAY" )>=0 THEN
For those dates fulfilling these conditions, you would like to mark them as billable days/fiscal period if the date is a working day according to 1 and 0 entries in the factory calendar.
Note:
- Billable days are specific for customer, location and fiscal period.
- Working days in factory calendar are specific for location and fiscal period.
- Hence you will have the following filters for DATA() and RESULTLOOKUP()
DATA([d/MEASURE]="IS_BILLABLE_DAY", [d/FISCALPD]=[d/WORKING_DAYS].[p/CALMONTH],[d/CUSTOMER]=[d/PROJECT].[p/CUSTOMER], [d/WORKING_DAYS]="#", [d/LOCATION]=[d/PROJECT].[p/LOCATION]) =
RESULTLOOKUP([d/MEASURE]="IS_WORKING_DAY",[d/FISCALPD]=[d/WORKING_DAYS].[p/CALMONTH],[d/LOCATION]=[d/PROJECT].[p/LOCATION],[d/CUSTOMER]="#", [d/PROJECT]="#")Now you have successfully utilized the factory calendar to determine which the billable days/fiscal period. Next, you will need to calculate the aggregated billable days/project so as to determine the ratio for the allocation in a later step. For the aggregation, simply duplicate the above script, but this time make the system does not separate the billables days into respective fiscal periods – instead, write them all into the virtual members you have created, i.e. #TOTAL_DAYS and #TOTAL_MONTH:
DATA([d/MEASURE]=#TOTAL_DAYS, [d/FISCALPD]=#TOTAL_MONTH,[d/CUSTOMER]=[d/PROJECT].[p/CUSTOMER], [d/WORKING_DAYS]="#", [d/LOCATION]=[d/PROJECT].[p/LOCATION]) =
RESULTLOOKUP([d/MEASURE]="IS_WORKING_DAY",[d/FISCALPD]=[d/WORKING_DAYS].[p/CALMONTH],[d/LOCATION]=[d/PROJECT].[p/LOCATION],[d/CUSTOMER]="#", [d/PROJECT]="#")Make sure you close your script:
ENDIF
ENDIF
ENDFORNow let’s start defining how the allocation should run. We need first to define the ratio. In its simplest form the calculation looks like this:
Total billable days for the month/Total billable days for the entire project
Translating that into advanced formula:
RESULTLOOKUP([d/MEASURE]="IS_BILLABLE_DAY", [d/WORKING_DAYS]="#")/RESULTLOOKUP([d/MEASURE]=#TOTAL_DAYS, [d/FISCALPD]=#TOTAL_MONTH, [d/WORKING_DAYS]="#")You need to write that resulting ratio into the model. Hence add a DATA() command to the beginning and you will end up with this:
DATA([d/MEASURE]="RATIO", [d/WORKING_DAYS]="#") =
RESULTLOOKUP([d/MEASURE]="IS_BILLABLE_DAY", [d/WORKING_DAYS]="#")/RESULTLOOKUP([d/MEASURE]=#TOTAL_DAYS, [d/FISCALPD]=#TOTAL_MONTH, [d/WORKING_DAYS]="#")Now you just need to use the ratio to calculate the proportion of the total charges that should be allocated, by multiplying it with the total charges for the whole project:
DATA([d/MEASURE]="ALLOCATED_CHARGES", [d/WORKING_DAYS]="#") =
RESULTLOOKUP([d/MEASURE]="RATIO", [d/WORKING_DAYS]="#")*RESULTLOOKUP([d/MEASURE]="TOTAL_CHARGES", [d/WORKING_DAYS] = "#",[d/FISCALPD]=[d/PROJECT].[p/START_MONTH])Note: Remember for your Analytic Application end user UI you have defined for the total charges to be written into the start month? You have defined previously the following: "FISCALPD" : "[FISCALPD].[YQM].&[" + DateUtils.yyyymmdd(DateRangePicker_StartEnd.getDateVal()).substring(0, 6) + "]", Hence here you need to filter down to the start month of your project for the total charges. |
And wow! You are done! You should have something that looks like this now for your second advanced formula step:
// Restrict scope for the "PROJECT" dimension down to the project selected in prompt. Restrict scope of working days down to the year 2020.
MEMBERSET [d/PROJECT]=%Project%
MEMBERSET[d/WORKING_DAYS].[p/YEAR] ="2020"
MEMBERSET[d/FISCALPD] = "202001"to "202012"
//Set up virtual member to store the sum of billables days for the entire project, and per month.
VARIABLEMEMBER #TOTAL_DAYS OF [d/MEASURE]
VARIABLEMEMBER #TOTAL_MONTH OF [d/FISCALPD]
//Loop through working days and if the date falls within project timeframe, add it to the count for billable days
FOREACH [d/PROJECT]
IF DATEDIFF( [d/PROJECT].[p/START_DATE] ,[d/WORKING_DAYS].[p/DATE] ,"DAY" )>=0 THEN
IF DATEDIFF([d/WORKING_DAYS].[p/DATE] ,[d/PROJECT].[p/END_DATE],"DAY" )>=0 THEN
DATA([d/MEASURE]="IS_BILLABLE_DAY", [d/FISCALPD]=[d/WORKING_DAYS].[p/CALMONTH],[d/CUSTOMER]=[d/PROJECT].[p/CUSTOMER], [d/WORKING_DAYS]="#", [d/LOCATION]=[d/PROJECT].[p/LOCATION]) =
RESULTLOOKUP([d/MEASURE]="IS_WORKING_DAY",[d/FISCALPD]=[d/WORKING_DAYS].[p/CALMONTH],[d/LOCATION]=[d/PROJECT].[p/LOCATION],[d/CUSTOMER]="#", [d/PROJECT]="#")
DATA([d/MEASURE]=#TOTAL_DAYS, [d/FISCALPD]=#TOTAL_MONTH,[d/CUSTOMER]=[d/PROJECT].[p/CUSTOMER], [d/WORKING_DAYS]="#", [d/LOCATION]=[d/PROJECT].[p/LOCATION]) =
RESULTLOOKUP([d/MEASURE]="IS_WORKING_DAY",[d/FISCALPD]=[d/WORKING_DAYS].[p/CALMONTH],[d/LOCATION]=[d/PROJECT].[p/LOCATION],[d/CUSTOMER]="#", [d/PROJECT]="#")
ENDIF
ENDIF
ENDFOR
//Define the ratio of each billable day to total billable days
DATA([d/MEASURE]="RATIO", [d/WORKING_DAYS]="#") =
RESULTLOOKUP([d/MEASURE]="IS_BILLABLE_DAY", [d/WORKING_DAYS]="#")/RESULTLOOKUP([d/MEASURE]=#TOTAL_DAYS, [d/FISCALPD]=#TOTAL_MONTH, [d/WORKING_DAYS]="#")
//Derive the portion of the total charges to allocate to each month
DATA([d/MEASURE]="ALLOCATED_CHARGES", [d/WORKING_DAYS]="#") =
RESULTLOOKUP([d/MEASURE]="RATIO", [d/WORKING_DAYS]="#")*RESULTLOOKUP([d/MEASURE]="TOTAL_CHARGES", [d/WORKING_DAYS] = "#",[d/FISCALPD]=[d/PROJECT].[p/START_MONTH])Save your data action. Add this to your application now, which you have created in step 2 of this use case.
Back to the application, triggering the data action now will prompt you to enter the desired project. Choose any existing project. Once you click ok, you can observe how the total charges are being allocated across the billable days in your monthly charges overview table.
And congratulations! You have just built a working planning tool for creating projects and allocating charges across defined fiscal periods!
If you have gotten a kick out of this just like I did, and plan to explore further potentials of the analytic designer or other data action functionalities, here are some other blog entries you might want to take a look at:
- SAP Managed Tags:
10 Comments
