Count days

Unknown

Hello, everyone, I need your help.
In order to calculate indicators, I need to count the number of days of the current month. I determine my current month of work via a screen input.
I would also like to know how to determine the first and last day of that month.
I thought of an input screen to manually fill in every beginning and end of month but I would like an automatic calculation.
I have loaded release dates in a date format cube, I am trying to calculate the difference between the last day of the current month and the release date (which occurs during the same month).
The calculation I am trying to put in place is the following.
Number of days of month – number of days between date of release and end of month
I was thinking of an MXC cube, but we can’t count the entity months.
Is it possible to count the number of days?

Paul Wyatt

Hi Pierre Rosnarho

 

Quick response: As a start point, you can achieve all you need to do using 1 entity, 1 procedure and 2 cubes*, with Nexel applied to the second cube.  I've attached a simple model to illustrate some of what you want.  If you do not know how to use Nexel, access the online manual and use the community to find the formulations for use with Nexel.

 

A little more explanation

What you're asking for is beautifully achieved with NEXEL in which BOARD enables the user to write to cubes and carry out on-the-fly calculations.  Figures, dates and text - it's all good, you do need to be aware of some nuances when using NEXEL and user inputs but it's straight forward.  I'll attach a model that you can play with and use to develop your queries if more come from this.

 

(Please Note, the calculations above are 1.the date, 2. The serial of the date, 3.  The first day of the month of the date, 4.  The last day of the month of the date.  5.  The number of days between 2 given dates; in this case it is the 1st and last of the month but that is irrelevant for the calculation as you can also show result in months, years, minutes or parsecs ).

NEXEL FORMULATION

 

I do not currently have time to do a how-to so here are all the NEXEL formulations used. 

member	User Input		member	User Input	Add Block	NEXEL Formula	Notes
Input1	09/04/2019		Input1	09/04/2019	09/04/2019	=[@a;*;*]	Simple copy of cell content
Input2			Input2		43556	=EOMONTH([@b;Input1;*],-1)+1	Formula works BUT converts output to numeric and not date format due to the [+1] operation
Input3			Input3		01/04/2019	=TEXT([@b;Input2;*],"d/MM/yyyy")	Converts above numeric output to date format - (can be incorporated into 1 formula
Input4			Input4		30/04/2019	=eomonth([@b;Input3;*],0)	End of month
Input5			Input5		30	=DATEDIF([@b;Input3;*],[@b;Input4;*],"D")+1	days between 2 given dates

 

Using the link to the NEXEL pdf, one can see the immense amount of formulation that is valid and soon realise that you can do anything here - Nexel's true brilliance is to take what we all know, EXCEL formulation, and apply it in BOARD to create very complex dynamic calculations so you don't have to build convoluted cubes and entity structures that are impossible to hand over to fellow developers.

 

SETUP - Initial steps (Less Nexel Setup - for now):

 

1.   Create a single entity with five members E:[User Input]  M:[Input1], [Input2]...[Input5]

2.   Create Cube1* - the input cube.  Use the entity from step 1 and ensure that the datatype is set to DATE.  This cube will be used to input your dates.  A procedure will then carry them to the target cube where Nexel will transform them into what you want.

3   Create Cube2* - The Target cube.  Use the entity from step 1 and ensure that the datatype is set to DATE.  This cube will form the dataview with a NEXEL structure and will perform the calculations you want on the dates you submit from Cube1.

4.   Create a screen

5.   Create dataview1 - Use the input cube, set to enable dataentry, axis row on User Input and to show all:

6.   Enter a date into the area for Input1 - note the calendar pop-out (pretty nifty no?)

7.   Create dataview2 - Use the Target cube, axis row on User Input and to show all:

IMPORTANT: Set Formulas in the FUNCTIONS Tab of the dataview to the Deepest Entity Rule (This allows you to create a pseudo spreadsheet cell structure where each cell can hold a unique formula).

8.   Select capsule Procedure Modelling

...And create the following procedure (NB: If you can't see something in the image below, you don't need to set it).

 

9.   Return to Screen

10.   Create a button & embed the procedure:

11.   Press it to populate the target cube.

12.   Using the Target Datview, access the sliding toolbar and select  - you were always curious about this one, weren't you?

 

13.   Now you need to use Nexel - here are some links to help:

   a.   Training by BOARD's very own  Peter Foulkes: https://boardeducation.talentlms.com/unit/view/id:2327 

   b.   NEXEL Formulations:   https://community.board.com/docs/DOC-1241-nexel-complete-formulae-library

 

14.   Note that you create the new block from within NEXEL.  However, you must then pop outof the NEXEL editor and change the data type of the new block as DATE before you re-enter NEXEL to enter the formulas - I told you there were 'nuances'.

 

Now, I'm not forsaking you Pierre.  I've attached the model for you to pull and prod too.

 

By the way, the attached zipped file was created using the hotkey combo [Shift][Ctrl][Alt] + [T] - useful feature don't you think?

 

Reply when you've completed these steps and I'll assist with moving forward where needed. 

 

If this response resolves your query, please mark as CORRECT in order to notify future community users that this was a solution. 

 

Regards

 

Paul Wyatt

-----------------------------------------------------------------------------------------------------------------------------

*We use 2 cubes, Cube1 to accept data entry and Cube2 to process the value using Nexel.  The data is transferred using a capsule procedure.  We do this for 2 reasons, even though we can try it with only one data entry enabled cube.  The first is best practice - we have a stable datasource - Cube1.  The second is to avoid an error caused when using NEXEL with a data entry enabled cube (...ok, the second reason is actually the first but shsssss, don't want to hurt her feelings?).

Unknown

Hi Paul Wyatt - Avison Young UK

Thanks a lot for your help, Paul, I have reproduced your model to determine the number of days with success. I would now like to know if it is possible to store the data (number of days) in a cube. Indeed, I would like to use that data again to make a calculation.

Paul Wyatt

PLEASE NOTE: This is a complete re-write as my original response to the second query as it was incorrect.

 

Hi Pierre,

 

Yes you can store the values in a cube as you calculate the values (remember that I am providing a recommended solution illustrated to show steps, not the one you will use in your solution).

 

You will need:

1. Entity 1 - [User Input] with 5 members; Input1, Input2...Input5
2. Cube 1 - [Data Entry] with [User Input] Entity and datatype: DATE
3. Cube 2 - [User Target Dates] with [User Input] Entity and datatype: DATE
4. Cube 3 - [User Target Values] with [User Input] Entity and datatype: DOUBLE
5. Button.
6. Procedure.

 

Setup:

 

DATAVIEW 1 - Data entry 

1. Create 1 dataview with Cube 1, entity on row axis, data entry enabled and SHOW ALL.
2. Enter a date into this object and save the data entry - you are seeding the cube as it will only be used to receive 1 entry and so you will not need the four other members to show.
3. Re-enter the dataview and disable SHOW ALL - You now have a seeded dataview displaying only 1 value.

 

BUTTON

1. Create a button and place it on screen - this will run the procedure which can only be created once the NEXEL calculations have been created and saved under a name.

 

DATAVIEW2 - This view will contain all 3 cubes, have data entry on 2 and process all the NEXEL calculations.  All actions are carried out by the procedure.

1. Create another dataview and configure as follows:
2. 
3. Enable Data Entry on blocks b and set formulas in to Deepest Entity Rule
4. Enable Data Entry on blocks c and set formulas in to Deepest Entity Rule
5. 
6. Enter Dataview [Fx] to create NEXEL calculations
7. Enter NEXEL Calculations into [User Target Dates - User Input 1,2...5].
8. Enter NEXEL Calculations into [User Target Values - User Input 2 & 5] - you can simply enter the address cell of the adjacent cell in place of the complete formula again.
9. Select SAVE TO LIBRARY - this allows you to run the NEXEL calculations from a procedure.  Savename = Save Values
10. Select to WRITE INPLACE - This causes the values to be written to the data entry enabled cubes when the procedure is run  

 

PROCEDURE

1. Create procedure [Transfer from Input cube to Target Cube] as follows:
2. 

Step:

1. Save all data entries - this is a failsafe that ensures new date entered is always saved fregardless of whether dataentry writeback is used by user.
2. Write the date to the first cube.
3. Run NEXEL calculations - This now computes, based on the user input date, all values and saves them to the cubes*
4. Refresh screen.

 

Application**

1. Return to screen
2. Select button settings
3. Configure action: [transfer from Input cube to Target Cube] 
4. Save capsule
5. Configure screen as required - below is an example where all dataviews have hidden scrollbars and toolbars to minimize needless screen responses - see image below

• IMAGE: Dataview Graphic Options
• 

 

Note I've included 2 additional dataviews to display the actual data saved.  when looking at the dataview with the NEXEL routine, it can be seen that figures are displayed.  However, as the cube datatype is DATE, these values are saved correctly - (big point to note here and one I forgot about too).  This is why the second cube is required for acting on those values as it is of datatype DOUBLE.

 

I hope that this is useful and you can see the versatility of BOARD dataentry and NEXEL for creating on-the-fly alorithm blocks used to manipulate date based calculations and expanding its use to create calculable values for further use.

 

It should also be noted that I have now tested this solution in Web and noted that this configuration does not produce a calendar tool for inputting dates on Web - this may produce issues to local date formatting and require you to determine the correct one to use.  I have not yet tested in 10.5.

 

Regards,

 

Paul Wyatt

 

Attachment:

1.   BOARD 10.1.4 hbmp & cpsx

---------------------------------------------------------------

* A NEXEL nuance is that it will calculate based on a value from a cube - the input date - but it will not write those results to a cube unless WRITE INPLACE is used and the routine saved so that it can be triggered in a procedure.

** It should be noted that this solution does not take into account multiple users.  A change in the input date and subsequent calculations will be seen by all users and can be changed by any one of them.  To prevent this, the application of @User security would be required in order that each user could run independent and isolated calculations on their own input dates.

Unknown

Thanks again Paul Wyatt - Avison Young UK. I followed your instructions to the letter but I couldn’t save the data.
As you can see below:
On the left screen, I did store the data in a backup cube.
But on the  right when I make a Dataview of this cube, it’s empty.

Paul Wyatt

Hi Pierre,

 

Sorry about that.  I really should not try to respond with a rushed how-to between work tasks.  I will correct my second response later however, attached is a capsule that should help you to understand the process required to create dates and values once you've entered a seed date.  The NEXEL is performed after being called in a procedure.  The values are then saved in 2 identical cubes but one is for DATES and the other for VALUES.  Hopefully it will make sense once you look at the attached model.

 

Instructions:

1.   Enter seed date in left hand cube.

2.   Select [Button].

3.   Verify that right hand cubes are as expected results.

 

Unknown

HiPaul Wyatt - Avison Young UK,

thank you for your help. This is the solution for my question.

 

Regards,

Pierre Rosnarho