IF list item 'x' THEN 'do this' ELSE...


I am trying to do an IF statement within a module dimensionalised by a List ('Volumes') which has 2 members: Retails and Wholesales.


I need to do a particular calculation depending on if the 'Volumes' are Retails or Wholesales.


I have tried IF Volumes.Retails THEN x ELSE y but this not work, giving the following error message:


The formula for 'XP 05 Derivative Mix'.Mix is invalid:
First argument for 'IF' function must have BOOLEAN format not LIST:Volumes


Can anyone help / advise?




Best Answer

    Answer ✓

    Hi Ricardo,


    Formula Correction, try this


    IF ITEM('Volumes')=Volumes.Retails THEN X ELSE Y


    ~Vignesh M


  • Super, thanks for the quick reply!
  • Hi @Riccardo @VIGNESH.M 


    For best practice (Refer to 2.02-12 of Planual), please avoid hardcoding of list member, and do the following instead

    Screen Shot 2020-01-06 at 10.36.22.png




  • Hi @Riccardo 


     @VIGNESH.M  reply must work, but note that it is hardcoded. It is ok, if the list "Volumes" is fixed and does not change with time. If not,


    Please create a system module for Volumes (SYS VOLUMES) and keep your boolean line item there (RETAIL & WHOLESALE). Manually maintainted by the administrator.


    If 'SYS VOLUMES'.RETAIL then <<formula for RETAIL>> else if 'SYS VOLUMES'.WHOLESALE then <<formula for WHOLESALE>> else <<so on>>


    There are ways to minimize the nested IFs as well.




  • Hi @LipChean_Soh @ArunManickam ,


    I totally agree with you, we should not do the hardcoding like this and instead use the system module as mentioned in Planula for the best practices.



    Vignesh M

  • Thanks all, good points. This list is fixed and so hardcoding should not be an issue, but noted your points on best practice.
  • Hi @LipChean_Soh , I'm going to build off of your example in order to ask my questions. 

    - In your example, "Retails" and "Wholesale" are list members in a list called "L1 Channel"
    - I have a module with Applies To of "L1 Channel"

    - This module has a line item with formula of "IF ITEM('L1 Channel') = 'L1 Channel'.Retails THEN 1 ELSE 0

    - Previously I would forecast volume at "L1 Channel". Now I want to forecast volume at a lower level.

    - This lower level will be a list called "L2 Sub-Channel" with list members of "Mass Merchandise" and "Drug and Dollar"

    To enable the lower level of planning using "L2 Sub-Channel"...I have to:
    1) Remove my formulae
    2) Update the Applies To from "L1 Channel" to "L2 Sub-Channel"
    3) Revise my formulae to reference "L2 Sub-Channel"
    4) Enter my revised formulae into the Line Items

    1) If I used the Boolean Staging in your example, would that circumvent my "Issue" above?
    2) In general, how should I have staged my lists/modules to accommodate a future lower level hierarchy (e.g. I plan at L1 last year but need to plan at L2 next year)