Using Dimension Properties

[former_member409271]

Hi Experts

We use the property field of a dimension to work out different calculation. So for example, we use Evpro or EPMMenmberProperty functions to get the property value of a dimension and if that dimensions property is a certain value, a calculation is done, if that property is another value a different calculation will be done.

The issue we are having is that - at some point in time, the property of a dimension will change thus the calculation will change depending on that dimensions property.

When we want to view prior periods, we still want the calculation to be based on what it was before it changed.

Hope I am clear with my query. Is there a way to make the property of a dimension static at a certain time period? or a way around this using BPC or its functions itself?

Thanks

Aadil

[cecilia_petersson2 · Accepted solution]

I've been playing around with comments as you suggest. Column DF is Entity, DG is Entity property Insurance, DH is comment retrieved from database for the selected period(EPMCommentFullContext), DI selects database comment if found, else property, and DJ saves the value of DI to the database. This way, each time you run the report for a new period, the current value of the property will be saved to the database and can be used in a calculation. If run for a previous period, nothing will be changed.

However, a drawback is that it will require manual intervention if you have already run the report for a period and thus saved that period's property value, and you later in the month want to change the property value. Also, my report is simple with only one period. If you have several periods, many columns will be required. I think this might be a difficult one!

/Cecilia

[former_member409271 · Accepted solution]

Hi Cecilia

Thank you soo much for this.

I think I can work with this as my solution. I see that it can become difficult but I now have a base to start from

Many thanks

Aadil

[cecilia_petersson2]

Hi Aadil,

Have you thought of writing a logic script and saving the calculated value to the database instead? If you ensure that previous periods don't get recalculated (by locking periods or running the calculation for a selected period only), you will have the values as they were calculated at the time.

/Cecilia

[former_member409271]

Hi Cecilia

thanks for the reply.

We have been saving the calculated value in the DB, but this is not ideal for us either as other factors in the calculation keep on changing and thus we have to keep on updating prior months on the db and cannot count on just changing the calc on the template we use. This is for Solvency reporting and thus the calculations keep changing.

I was thinking about submitting the dimension property value as a comment, that way, the comment will be at a specific time. Then doing the calc based on the value of the comment submitted.

Though I have not worked with comments on BPC and not sure if my thinking is way off for this query.

Regards

Aadil

[former_member186498]

Hi Aadil,

not sure I've understood your request, but if you have to choose between 2 or few properties maybe it's possible what you ask adding a property in time dim that containd the name of the property for this period, i.e. "A" until march and "B" from april. and use the name to point the right property on your dim.

Regards

     Roberto

[cecilia_petersson2]

Hi Aadil,

Could you please share a screenshot of the report you'd like to build!

/Cecilia

[former_member409271]

Hi Roberto

I simplified it in my example as it is not just one property that could. We have about 10 different properties for an entity that could be a certain value. The calc then says if property1 of an entity is this and property2 is this then do this else if property3 is another value then do something else.

Also, Each entity is assigned these properties, so at a certain time a property can change for just one entity and remain the same for other entities.

Regards

Aadil

[former_member409271]

Hi Cecilia

The report or template is a bit complex or large to show over a screenshot.

but here is one of the simple formula we used in the report on one cell

1. we first used the evpro function to get the property of a entity. (the property value here can either be "A (Insurance ST)" or "A (Insurance LT)"

2. then we use normal BPC to populate data in the report

3. then based on this data from points 1 & 2 we used the following formula

=-IF($M21="A (Insurance ST)",AM21+BC21,IF($M21="A (Insurance LT)",AU21+BK21,CM21+CN21+DC21+DD21))

The entity dimension's property could change at a point from A (Insurance ST) to A (Insurance LT), what we are looking to do is, if we refresh the report at a time period when the property was A (Insurance ST), the correct result will be given, and if we refresh the template after the time period when the property was changed, again the correct result must be given.

We have many other formulas or calculations like the one above referencing different properties of the dimension that may change at a time period.

Regards

Aadil

[former_member228522]

Hi,

You can go for 2 properties with same values. In one property the values will be static and in another property you can change value.Then you can use static property value in your calculation.

Best Regards,

Deepak

[former_member409271]

Hi Deepak

thanks for the reply.

I don't think this will work for us, as the calc uses the property value at a specific time.

So for month march and prior,  the property was "A" the calc would be if A then multiply by an amount.

in Month April , the property changes to "B", so now the calc will multiply the value by another amount.

When we pull data for March and prior we still want to see the calc as it was when the property was "A" and when we pull data for April and going on we want to see the calc as it is when the property is "B".

The property could change again in future months and the calc will then be done according to the new property.

Regards

Aadil