Summary options with LOOKUP/SELECT functions - wrong percentages

PrevContributor
PrevContributor
in Modeling

Hi all, I've had some trouble lately with combining the LOOKUP/SELECT function and the summary function, when working with percentages. Let me show the issue with a concrete example. 1) First of all I have a product structure: [img height="75" style="width: 218px;" width="230"][/img] 2) For each product I calculate the Client Rate in my Formulas Module with a Summary set as Formula image 3) Afterwards I have a module that collects the numbers, a subset of the module. I can still select Formula for Summary with the function Collect 4) Now I will create a comparison report, however all percentages for parent categories in the product structure are wrong. This is caused by the fact that I cannot select Formula for Summary, while using the following functions: - Dec Y-1 = 'Details CB - Collect YTD/Periodic %'.YTD[LOOKUP: Selections.'Dec Y-1', SELECT: custom version.Actual] - Y-1 =  'Details CB - Collect YTD/Periodic %'.YTD[LOOKUP: Selections.'Period Y-1', SELECT: custom version.Actual] - ... image So my question: Is there a possibility to lookup up the right percentages? As for now the only summary option is for example Average, however it is not weighted, so the results are wrong. I would loved to just use the Formula summary option, however it does not function with LOOKUP's. Many thanks and best regards, Mickel  

Tagged:

Answers

  • PrevContributor
    PrevContributor
    I have been thinking about the issue and I found a solution by removing the lookup function, however to keep it feasible with the amount of IF formula's, I need a bit more help.

    Currently my solution is to recalculate the percentages in the final module, however I need IF function for every line item, as all of them are calculated differntly.

    For one item:
    IF ITEM(LIS Details CB %) = LIS Details CB %.Commercial margin % THEN 'Details CB - Comparison Report Per Product'.'Y-1'[SELECT: LIS Details CB.Commercial margin] / 'Details CB - Comparison Report Per Product'.'Y-1'[SELECT: LIS Details CB.Average balance] / 'Details CB - Comparison Report Per Product %'.'Y-1'[SELECT: LIS Details CB %.Macor] ELSE 0

    I though a solution for the if functions, however it did not work:

    1) A dynamic selection:
    Where I try to replace this [SELECT: LIS Details CB.Commercial margin] by this [SELECT: Select balance] with format the list LIS Details CB, however Anaplan does not recognize it as a list.

    Of course, the first solution with 7 IF functions works, however I have other reports with more line-items, then it would not be a feasible solution. Any ideas, insights or thoughts are welcome, even if it's not a full solution.

    Thanks 
  • Misbah
    Misbah

    I too tried to find a workaround but it gets very complicated and most of the times it doesn't work. Removing Lookup from a formula is not a viable solution because you lose the dynamic property along with it.

Categories