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]

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 Member]

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