# Consilidation v Rules

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # Consilidation v Rules

Hi All,

I am creating a KPI database which is reported on daily.

For each day we have a MTD & then a YTD.

I have set up 4 dimensions as below

KPI Indicators
Days
Months
Years

Now for the crucial bit. Some of the KPI Indicators are volumes so I can use consilidation & the others are average percentages. I presume I will have to use rules for these.

Is this correct ?

Is there a simple way of creating the rule or will I have to add each day from 1 to 31 ?

Given that I have about 35% of the KPI indicators as % will all these rules slow everything down.

Any advice / help would be greatly appreciated.
• # RE: Consilidation v Rules

Hi Neal267,

I have been working on a similar issue but only going down to the level of Month. There are going to be endless ways of solving this based on your own requirements but here is what I think. Performance has been excellent for these calculations.

There are a couple of things to consider first:
- do you want to include 0 value cells in the calculation?
- are the MTD and YTD values you talk of consolidations or numeric elements in the Month dimension?

My response is broken into a series of steps as defined by the bold headings.

Measure Dimension
If you don't want to include 0 value cells then you need to consider adding a measure type dimension to your cube which would have elements similar to the following:
- Data Entry (used for capturing the value)
- KPI Value (used for displaying a final value)
- Populated (flag for tracking number of populated cells)

The element Populated is a flag which checks whether the KPI Value is 0 or not, this can then be used for a more accurate average calculation.

### Source Code

1. ['Populated'] = N: IF(['KPI Value']!=0,1,0)

Attributes
Create an attribute against the KPI Indicators dimension called Calculation I then use this value to test whether the calculation requires application of the average formula. If you don't use this type of approach then you would need to test each KPI.

Average Rule
Based on your initial posting I would need a lot more information to make this rule work exactly for you but the following may give you an idea of what you need to do. Note the reason I have a Data Entry mesaure and a KPI Value measure is so that I can miniomise the number of rules. You could potentially just use one measure but you would need to define the rule for each consolidation if you have multiple hierarchies - I hope that makes sense.

### Source Code

1. ['Data Entry']= STET()
2. ['KPI Value'] = C: IF(UPPER(PALO.DATA("Server","#_KPI Indicators", "Calculation",!'KPI Indicators')=="AVERAGE", IF(['Populated']==0,0,['Data Entry'] / ['Populated'], STET())[/code
3. ]If you go without the Data Entry measure and want to perform the calculations against the same KPI Value measure then depending on your data structure you may need rules at the consolidated level which look like this:
4. [code]['KPI Value', 'Aug YTD'] = C: IF(UPPER(PALO.DATA("Server","#_KPI Indicators", "Calculation",!'KPI Indicators')=="AVERAGE", IF(['Populated']==0,0,(['KPI Value', 'Jul']+ ['KPI Value', 'Aug']) / ['Populated'], STET())

Hope this helps.

Rod