Vizlib Home Try Vizlib
Welcome
Login

P&L reporting using the Pivot Table

I am sure I am not alone in wanting to present a Profit & Loss report and your Pivot table extension has an undocumented example included in the package. 
Can someone demonstrate (video or document) how we can use this extension for this purpose?

Thanks in advance

Alexis


  • Step 1 – add your P&L line structure as inline data load in the data load script:

    PLMetricName:

    Load * Inline [

     PLMetric, PLSortOrder

     Revenue, 1

     Cost of Sales, 2

     Gross Profit, 3

     Etc., etc.,

     Direct Operating Margin, 13

    ];

     

    Step 2 - in the front end create variable vPLMetricMatchList using the PLMetric values, e.g.:

    'Revenue','Cost of Sales','Gross Profit',’etc.','Direct Operating Margin'

     

    Step 3 - Create new pivot table with ‘PLMetric’ as dimension.

    Step 4 – use pick(match()) to calculate the measures, e.g. Actual/Budget/Prior Year, Variances $, Variances %

    =pick(match(Metric, $(vMetricMatchList)),

    $(vRevenue),

    $(vCOS),

    $(vGrossProfit),

     

    … //add the rest of the metrics in the same order as they appear sequentially in vMetricMatchList variable.

    )

     

    Note: You can use excel spreadsheet to manage the P&L calculations and translate them to variables in the load script, e.g:

     

    Expressions:

    LOAD

     VariableName,

     VariableExpression

    FROM [lib://dataconnection/Variable list_example.xlsx]

    (ooxml, embedded labels, table is Consolidated);

     

    Let vNumExpressions = NoOfRows('Expressions');

    For vI = 0 to (vNumExpressions - 1)

          Let vVariable = Peek('VariableName',vI,'Expressions');

          Let [$(vVariable)] = Peek('VariableExpression',vI,'Expressions');

    Next

Login to post a comment