Month selection based on a Start Date and End Date

Previous Member

In order to create a Project (i.e. promotion) management function (which allows end users to add and edit promotions in Board), I've created two Date type cubes for a project Start Date and End Date respectively, which contain only one dimension - Project ID. The requirement is after both dates are entered by users, the month selection adjusted accordingly for the project so the dataview below only shows the relevant months.

The way I can think of is to introduce a cube as the dynamic selection for Month which has two dimensions - Project ID and Month. Each time when Start Date and End Date are saved, the values are extracted to SQL so we can generate month records for the project accordingly, so the values for those months within the date range will be set to 1, otherwise is 0. Then we load the data back to Board.
Does anyone have a better way to achieve this? Thanks a lot.

 

Kevin

Previous Member

Hi Kevin,

 

I think a bit more detail around the requirements is needed to give you the best answer, but if a user is just going to select time periods in order to constrain where they want to do data entry on 1 specific screen for 1 specific project, why even store the dates, and simply use the built-in calendar or month selector to allow the end user to constrain the data entry grid on the fly? If the start date and end date are needed for other reporting purposes, or you need to store the valid intersections in a cube for some reason, you could do something similar to what you are suggesting with only data flows, and avoid using SQL altogether. What you would need in that case is a date cube dimensioned by whatever time entity makes sense (it looks like Month based on your screenshot, unless they can select partial months or do data entry by week or day, for example), and populate it with the first or last time period as appropriate (imagine a date cube called "Last day of the Month", dimensioned by Month only, and populated for every Month in the database with the last day of that Month, which would be something you could do as a 1-time, or infrequent data load.) Then you can write a data flow with the logic you describe: IF (AND(Start date < Last Day of the Month, End date >=Last Day of the Month), 1, 0) and put a dynamic select on the screen using that cube or apply a selection to the screen, etc. Maybe something like that.

 

HTH.

Mauro Leoni

Hi,

also you could use the same 1/0 cube to block data entry based on cube (according with the dimensions of the cube where you want to write to of course) , this way you will have more control on where/when the user can write even in case of change on time selection 

 

Regards

MAuro

Linda Nodén

Hi,

Another solution would be to have the start/end dates as text cubes instead of using datetime as I find them quite tricky to work with. Then configure the data entry using suggested values by the entity you would like (day or month in your example). This way they can only choose between the date entities available in the database.

Then create procedure containing a nexel or a dataflow that uses the start/end date cubes to fill your cube for project horizon.

Federico Cazzalini

Hello Kevin,

have you already consider using something like this example?

Calculate days between first and last transaction with Nexel 

 

hope this helps,

regards,

federico

Previous Member

Thanks for your help, the solution works for me. Thanks.

Previous Member

Hi Federico,

 

Thanks for your suggestion, it's really useful. Thanks.

 

Regards,

Kevin