Actual vs Plan Reporting Issue (Different grains = double-counts)

rbalakrishnan

Hi All,

I have the following 3 Modules:

 

Module 1 - Staging (Actuals)

Lists - Supplier, Service Category, Time, Line Items (Amount)

 

Module 2 - Input Plan Data

Lists - Supplier, Service Category, Maintenance Category, Time, Line Items (Amount), Versions

 

Module 3 - Reporting Module

Lists - Supplier, Service Category, Maintenance Category, Time, Line Items (Amount), Versions

 

Amount here is a straight-forward formula - 

IF ISACTUALVERSION() , Module1.Amount, else Module2.Amount

 

ISSUE:

I have 4 members in the list - Maintenance Categories

 

Because my Staging Module (Module 1) doesn't have maintenance category, it pastes the same value to all "base level" members of the maintenance category list resulting in 4X the amount expected in the Reporting Module (Module 3).

 

I avoided using maintenance category in Staging Actuals because Actuals data is not received with maintenance categories; it's more a planning thing.

One method I thought of is to include an N/A member in the maintenance category list and merge module 1 and 2. However, I am not comfortable with that as I would like to have stand-alone modules for data loads (Actuals) and actual data entries (Planning) to ease the maintenance.

 

Happy to hear your suggestions to resolve this.

 

Cheers

 

 

Ari

Hi  

rbalakrishnan,

 

I understand you want to restrict the context of the target module to one maintenance category: you have several methods to do that.

 

Method 1: use SUM as an aggregation/mapping:

 

IF ISACTUALVERSION()

THEN

Module1.Amount[SUM:Variable]

ELSE

Module2.Amount

 

Variable = Line item with list format (Maintenance Category list)

 

Method 2: Hard code which Maintenance Category should receive the actuals

 

IF ISACTUALVERSION()

THEN

               IF ITEM('Maintenance Category') = Variable

              THEN Module1.Amount

              ELSE 0

ELSE

Module2.Amount

 

Variable = Line item with list format (Maintenance Category list)

 

Method 3: Mapping module / Create a module to map actuals

 

Module 4 - Systems Map Actual to 

Applies to: Maintenance Category

Line Item: Boolean Called "Mapping"

 

IF ISACTUALVERSION()

THEN

               IF Module4.Mapping

              THEN Module1.Amount

              ELSE 0

ELSE

Module2.Amount

 

Cheers

 

 

 

 

andrewtye

One question would be... why plan with Maintenance Category if you can't report against it?

But to do Actuals vs Plan reporting just take out Maintenance Category from Module Three and have summary settings set to SUM in Module Two as suggested by @Ari 

rbalakrishnan

Thanks for your reply. I had pretty much short-listed on Method 3 but wasn't sure if that was a recommended way. I will proceed with the same.

Cheers

rbalakrishnan

As a part of the re-engineering exercise, Actuals will eventually be available at the same grain as Plan. But as it stands, the source system doesn't give them that information.