9 Replies Latest reply on Aug 13, 2018 12:22 PM by Okechukwu Ossai

# How to fix the level (LOD) issues in my If formula

Hi All,

I want to use this formula but i have some difficulties:

IF [Product Line]= "CABLE" THEN [Moving_avg 6W]*13*1.25

ELSE [Moving_avg 6W]*13*1.12

END

I want to specific saying that, when Product line is cable, it used a different formula. But LOD has issues.

Under Product Line, there is product category, and Item numbers.

Thanks,

Helen

• ###### 1. Re: How to fix the level (LOD) issues in my If formula

Looks like you are trying to use Table Calculations in LOD, One can answer better with a packaged workbook attached to your question.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 2. Re: How to fix the level (LOD) issues in my If formula

HI Deepak,

Yes I am trying to create a dataset now.

I did use a table calculation for the “moving_avg 6W”

Is there anything you recommend me to try? Like fixed? Or use sum()?

1 of 1 people found this helpful
• ###### 3. Re: How to fix the level (LOD) issues in my If formula

I am afraid you won't be able to use a Table Calc inside LOD. Let's See Your Workbook first and where you got Stuck.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 4. Re: How to fix the level (LOD) issues in my If formula

Hi Deepak,

I am still creating the dataset but below is my working dataset.

The Moving_avg 6W is the table calculation based on the sales data. It takes the newest 6 weeks average of the sales.

I am trying to create a metric while I can multiple “Moving_avg 6W” with 13, and if the product is falling under Product Line = cable, then it multiple with *14

Is it feasible? If not, is there any other way you may suggest me to go around?

1 of 1 people found this helpful
• ###### 5. Re: How to fix the level (LOD) issues in my If formula

Let's Have Some Workbook Please. Take your time to create and post please.

1 of 1 people found this helpful
• ###### 6. Re: How to fix the level (LOD) issues in my If formula

Hi Helen,

From your description, above, looks like there is no necessity of a LOD expression.Can you post your definition for Moving_Avg_6W field?

1 of 1 people found this helpful
• ###### 7. Re: How to fix the level (LOD) issues in my If formula

Hi Helen,

What does the error message say? If the error is due to mixing aggregate and non-aggregate fields, then you can try;

IF ATTR([Product Line]) = "CABLE" THEN [Moving_avg 6W]*13*1.25

ELSE [Moving_avg 6W]*13*1.12

END

Hope this helps.

Ossai

1 of 1 people found this helpful
• ###### 8. Re: How to fix the level (LOD) issues in my If formula

Hi,

Thank you so much for your help.

May I ask why ATTR() will help?

Thanks and best regards,

Helen

• ###### 9. Re: How to fix the level (LOD) issues in my If formula

Hi Helen,

ATTR() in this particular context was used to aggregate the [Product Line] field.

This is required since a calculated field can only contain either aggregated fields or non-aggregated fields. Mixing aggregated and non-aggregated fields in a single calculated field will result in an error message.

Your formula contains an aggregated measure [Moving_avg 6W] and a non-aggregated dimension [Product Line], hence the error message.

The solution is either to remove the aggregation on [Moving_avg 6W] or convert [Product Line] to an aggregated field. I chose the latter option using the ATTR() function since it appears [Moving_avg 6W] contains some table calculation functions which must be aggregated.