Formula error


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.






  • Hi @AlexForsythe 


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




  • Hi @AlexForsythe 


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




  • Here is the blueprint for the Sales Trend V4


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



  • @AlexForsythe 


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



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

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




  • Hi @AlexForsythe ,


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