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

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

 

 

Best Answer

  • Ari
    Ari
    Answer ✓

    Hi  

     

    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

     

     

     

     

Answers

  • 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 

  • 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

  • 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.