How to get data only in the first month of a selection

For my planning tool I have two cubes for counting the value of the fixed assets: one with yearly figures and one with monthly figures.

the monthly figures cube is filled with investment data per month and depreciation per month.

I use a nexel writeback to calculate a closing balance per month. and the opening balance of the month is the closing balance of previous month. The starting balance of January however has already been calculated in the yearly figures and I want to push this amount tot he monthly figures as adjustment opening (opening is a formula and cannot get data through a data flow).

Now I use an interactive selection to select the month January when the data comes from the yearly cube. But this is not a final solution.

Is there a way that I can tell the data flow that I only want data pushed to the first month of the year (I already have a selection on entity year and selected only 2019 through a settings cube )?

I tried it with the entity month in the data flow and then an if-statement: if(right(b,2)="01",a,0)

However that did not work

Find more posts tagged with

Accepted answers

All comments

Previous Member

Hi Coos Bos,

there are several approaches to select a specific month within a procedure.

Have you tried to set the selection by using dynamic select based on a cube which you can either for example configure in an administration area or define by using a datareader?

Otherwise, you can use a custom time entity above the month entity to select for example the month "January" which might look like this:

This way the selection of the month is indepedent of the year - assuming the time tree is well maintained

Hope this gives you some inspiration!

Best regards,

Bettina

Coos Bos

Dear Bettina Clausen,

thank you for your reply.

It was interesting to read and I have tried several things, also with the time functions.

These however did not help.

The custom time entity, I did not try, as I am afraid that this also not works due the the already existing dynamic selection on the budget year (is a cube with 2019 selected). adding a dynamic selection on another time entity will release the year selection and in this case I need two time entities to be selected (year and month).

Therefore I created a cube which is called starting month of budget year and in this one the administrator can set the starting month (= January) of a year.

Now it works good.

Kind regards, Coos

Björn Reuber

Hi,

there is also a different solution using a "Selection cube" based on Month. This cube only contains a 1 in every January. Now you can use this for SBO on Month and allways getting the January of a certain year

Regards

Björn