Formula help

CommunityMember113484
CommunityMember113484
in Modeling

Hi,

I need some help with a formula/modelling. I have a module dimensioned by Products and returns (row) and measurement periods (columns) (1 yr, 3 yrs etc). My line items are actual performance and benchmark performance (see screenshot below). Products may track performance against more than one metric (e.g. Returns A and Returns B).  However, in my final report, I want to only select 1 per product (so I've created a boolean for this).  How do I create a formula which states, depending on the boolean, select the actual and benchmark performance for that product?

Thanks

CommunityMember113484_0-1602687276137.png

 

Tagged:

Comments

  • Ingilavicus
    Ingilavicus
    Hi,

    I guess something happened while posting, but could you share that screenshot? Would help to better understand the question.
  • CommunityMember113484
    CommunityMember113484
    Strange - see now! Thanks
  • M.Kierepka
    M.Kierepka

    Hi

     

    I propose one of two options:
    1. The report would be input view, but filtered based on Boolean ticked as selection - you can hide unused columns in the report.

    2. If you want to have it in the different module, without dimesnion of returns, in the input make line item (lets call it "Picked"), which will have format of Returns, formula "IF Boolean THEN (Item(Returns)) ELSE BLANK" and summary as either First or Last Non-Blank. Then you can just use it as a source for first column (lets call it "Selected Return", again format of Returns) in your report (which, basing on selection, displays "Return A","Return B" or whatever has been selected, and you now have source for your LOOKUP. So line item in output will be just "Input.Source Line Item[LOOKUP:Selected Return].

     

    Side note: Remember to add DCA to not allow for multiseletion of Booleans - to be honest, instead of Booleans, it would be better to just use list selector to choose from Returns for each product - this way you should choose option 2. as you can just LOOKUP basing on this selector, and you don't have to use DCA to force user to select only one (in Anaplan you don't have something like radio buttons, which allow you to select only one Boolean).

Categories