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.
Answers
-
Can you attach a screenshot of your module blueprint, showing the line items, formula, Applies To, Time?
Thanks,
LipChean
0 -
Can you attach a screen shot of the module blueprint, clearly showing line item, formula, applies to, month, etc?
Thanks,
LipChean
0 -
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
0 -
Question, why are you using a list named Fiscal Period list and not the native Time list?
Rob
0 -
Because our fiscal calendar does not line up with the normal calendar.
0 -
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
0 -
Hi @AlexForsythe ,
Although you might have already figured this out, an ELSE condition is missing at the last in your formula.
regards,
vinayvm
0
