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?

 

 

Thanks

Best Answer

  • VIGNESH.M
    Answer ✓

    Hi Ricardo,

     

    Formula Correction, try this

     

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

     

    ~Vignesh M

Answers

  • 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

     

    Thanks,

    LipChean

  • 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.

     

    Thanks
    Arun

     

  • 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.

     

    Thanks,

    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. 

    Assumptions:
    - 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"


    Issue:
    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

    Questions:
    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)