I wanted to subtract two coloumns between a pivot in order to calculate the variance is it possible

Rahul123123123
Rahul123123123
in Modeling
Screenshot (5).png


Answers

  • AjayM
    AjayM

    Hi @Rahul123123123 ,

    If the second dimension you got under columns is native Versions, you might want to add formula against the 'Variance' version, in Version settings.

    image.png
    image.png


    Cheers!

  • rob_marshall
    rob_marshall

    @AjayM

    While that works and is probably the easiest way to accomplish this, it is not the best way as it breaks Planual Rule 1.02-02. A better way would be to create the "accounts" or the row members as a list or a line item subset and then have the "Versions" and Variance as line items. It will also save space as you will not have an extra version member (Variance) since we currently can't subset native versions.

    Rob

  • AjayM
    AjayM

    Totally makes sense @rob_marshall , thanks for reminding me about the Planual rule.

  • Rahul123123123
    Rahul123123123
    edited March 2023

    Hi I have tried this but it is not working, I have created the model by the name of new budget but it is not changing by creating general list as well.

    But no change please find the details of my variance analysis

    Screenshot (17).png
    Screenshot (18).png
    Screenshot (19).png
    Screenshot (20).png
    Screenshot (22).png


    its a trial version, please help with the subtraction

  • AjayM
    AjayM
    edited March 2023

    Hi @Rahul123123123 ,

    My initial recommendation would work in case you use native versions (Anaplan's default 'Versions'). And as @rob_marshall corrected me, my proposal is not efficient (and breaks Planual Rule 1.02-02) because that 'Variance' version (with a formula) might not be needed in other places of the model, and it would occupy unnecessary space, wherever you use versions dimension in a module.

    And as I noticed in your latest snapshots, you are not using native versions, but a general list called 'Budget Group'. In this case, you can follow Rob's approach, which I tried to elaborate below.

    Let's say 'New Budget' is our input module, where you have data for 'Actual' and 'Forecast' versions (list items of 'Budget Group' list). Please note that we can't calculate variance within this input module, using the below approach.

    image.png
    image.png


    Now create a line item subset ("LIS Var Analysis" in my example), for the module 'New Budget'. And include only those lineitems for which you want variance calculation:

    image.png
    image.png

    Now create another module ("Var Cal02" in my example) to calculate variance, with dimensions and lineitems as shown below:

    image.png

    I have basically created one lineitem for each of the 'budget group' list items, and defined formula for 'Variance' line item to give us the difference. Please note that if 'Budget Group' is a production list, you better replace direct references of list items in Actual and Forecast lineitems, with references from a SYS module without any dimensions, where you identify each list item:

    image.png
    image.png


    Now, on the UX, you would want to hide 'Planned' lineitem, and pivot & publish as below:

    image.png
    image.png


    PS: If you are using 'Budget Group' general list for versions, you might not need 'Variance' version and a formula in native "Versions".

    Cheers!

Categories