4 Replies Latest reply on Jan 25, 2018 11:38 PM by Humphrey Chapa

# Calculated Field

Have a if statement on my calculated field but it only gives results for the last part of if clause.. i.e only 5000, Shown below

IF  ATTR([MTD Sales]) >= 5000000 THEN 20000

ELSEIF ATTR([MTD Sales])>=4000000 THEN 150000

ELSEIF ATTR([MTD Sales])>=3000000 THEN 12500

ELSEIF ATTR([MTD Sales])>=2000000 THEN 10000

ELSEIF ATTR([MTD Sales])>=1000000 THEN 75000

ELSE 5000

END

Where could i be doing it wrong?

• ###### 1. Re: Calculated Field

Good morning - what is the formula for MTD Sales

Jim

• ###### 2. Re: Calculated Field

IF [date_invoice] <= [Today_Ammended] AND  DATEDIFF(('month'),([date_invoice]),[Today_Ammended])= 0 AND [category]="Retail"

THEN

[Net Sales .]

END

@jim Dehner

• ###### 3. Re: Calculated Field

OK Thanks

Here's how I read your formula

you are looking at period that is the current month and the category is Retail - and if the invoice falls in the period you want to flag the sales

then in your original formula you are checking that formula against a heard coded total for MTD BUT - the MTD has not been aggregated

in one of the 2 formulas you need to sum the net sales for the comparison

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 4. Re: Calculated Field

Thanks Jim, this worked!