Formula error

AlexForsythe
New Contributor

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.

 

 

 

 

7 REPLIES 7
LipChean_Soh
Certified Master Anaplanner

Re: Formula error

Hi @AlexForsythe 

 

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

 

Thanks,

LipChean

LipChean_Soh
Certified Master Anaplanner

Re: Formula error

Hi @AlexForsythe 

 

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

 

Thanks,

LipChean

AlexForsythe
New Contributor

Re: Formula error

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
Moderator

Re: Formula error

@AlexForsythe 

 

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

 

Rob

AlexForsythe
New Contributor

Re: Formula error

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

LipChean_Soh
Certified Master Anaplanner

Re: Formula error

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

vinayvm
Certified Master Anaplanner

Re: Formula error

Hi @AlexForsythe ,

 

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

 

regards,

vinayvm