Formula error

AlexForsythe
AlexForsythe
in Modeling

My formula is not working. Here is what I have:

 

IF 'Sales Trend V4'.'2019A' = Gross Sales.'2019A' THEN 'Sales Trend V4'.'2019A'[SELECT: GL Name Sales.Sales] / 'Sales Trend V4'.'2019A'[SELECT: GL Name Sales.Cases] ELSE IF 'Sales Trend V4'.'2020A' = Gross Sales.'2020A' THEN 'Sales Trend V4'.'2020A'[SELECT: GL Name Sales.Sales] / 'Sales Trend V4'.'2020A'[SELECT: GL Name Sales.Cases]

 

I'm trying to divide 2019A sales by 2019A Cases THEN 2020A Sales by 2020A Cases.

 

 

 

 

Tagged:

Answers

  • LipChean_Soh
    LipChean_Soh

    Hi @AlexForsythe 

     

    Can you attach a screenshot of your module blueprint, showing the line items, formula, Applies To, Time?

     

    Thanks,

    LipChean

  • LipChean_Soh
    LipChean_Soh

    Hi @AlexForsythe 

     

    Can you attach a screen shot of the module blueprint, clearly showing line item, formula, applies to, month, etc?

     

    Thanks,

    LipChean

  • AlexForsythe
    AlexForsythe

    Here is the blueprint for the Sales Trend V4

    AlexForsythe_0-1622213621646.png

    Here is the blueprint for the module that I'm trying to do the calculation in

    AlexForsythe_1-1622213701991.png

     

  • rob_marshall
    rob_marshall

    @AlexForsythe 

     

    Question, why are you using a list named Fiscal Period list and not the native Time list?

     

    Rob

  • AlexForsythe
    AlexForsythe

    Because our fiscal calendar does not line up with the normal calendar.  

  • LipChean_Soh
    LipChean_Soh

    Hi @AlexForsythe 

     

    On top of what @rob_marshall  pointed out, i.e. using line items to represent time, there seems to be a lot of weird design and model building decisions in your modules and lists.

     

    For example, 

    1. Why does it seem like you have time presented in line items (2019A, 2020A, 2021A) and lists (Fiscal Period List) in the same module, i.e. Sales Trend V4?

    2. Why does it seem like you have 2 different lists representing Sales, i.e. GL Name Sales, Gross Sales?

     

    Anyway, to answer your original question, your formula isn't working because you're comparing a number with a list member, i.e. 'Sales Trend V4'.'2019A' is a number, Gross Sales.'2019A' is a list member in 'IF 'Sales Trend V4'.'2019A' = Gross Sales.'2019A' THEN'

     

    Thanks,

    LipChean

  • Vinay VaradarajM
    Vinay VaradarajM

    Hi @AlexForsythe ,

     

    Although you might have already figured this out, an ELSE condition is missing at the last in your formula.

     

    regards,

    vinayvm

Categories