5 Replies Latest reply on Jul 17, 2017 8:42 AM by César Martínez

# Accumulate a Calculation Within ElseIF Statements

Hi Everyone.

New issue I'm certain I'm missing what I need to do to workaround.

It's to compare monthly  bins this year verses last year in time periods from the current month going back.

Such as this month 2017 compared to this month 2016.  Then this month back 3 months 2017 compared to this month back 3 months 2016.

I have a nested IF statement which the first month bin is good, the 3 month bin is working BUT it starts from this month -1 accumulating back 3 months. Instead of sales for July + June + May, It's June + May + April sales.

Any ideas I could get it to keep the start at the current month?

Thanks.

I'm getting better at this attaching workbook examples etc.

Great, warm and helpful community!

• ###### 1. Re: Accumulate a Calculation Within ElseIF Statements

Well, when you are making the bins, the current month is not included in the group "3" because is already part of group "1" and can not be part of 2 groups at the same time. The alternative is create 2 fields and a parameter as the image below:

• ###### 2. Re: Accumulate a Calculation Within ElseIF Statements

Thank you Cesar.

I like to go that route but this table requirement is to bin up the months 1, 3, 6, 12

Current and past of the same period and the difference and the growth between them.

The parameter idea isn't going to help me unfortunately. I'm hoping there's another workaround I can apply.

Get Outlook for Android<https://aka.ms/ghei36>

• ###### 3. Re: Accumulate a Calculation Within ElseIF Statements

Well I discovered that was the only option.  Thank you Cesar.  I have bins for this past month, last 3 months, 6 months and 12 months.

On another note.

Is there a method to display all 4 bins in the table or will it all be locked at what is selected?

Thank you.

• ###### 4. Re: Accumulate a Calculation Within ElseIF Statements

Hi Jayson

I looked at your binning formula and it seems more than a little confusing and what Cesar has said is correct - your calculation was trying to put the first and the 13 month into both the one month and 3 month bins - that won't work because as soon as a record meets the criteria for an if clause it returns then THEN and goes no further (it goes on to the next record)

You need to set up 2 separate calculations one for the One month as

if DATEDIFF('month',[Order Date],{ fixed : MAX([Order Date])})< 1 then 1

elseif DATEDIFF('month',[Order Date],{ fixed : MAX([Order Date])})=12

then 1 else 0 END

and one for the Three month

if

DATEDIFF('month',[Order Date],{ fixed : MAX([Order Date])})<1

or

DATEDIFF('month',[Order Date],{ fixed : MAX([Order Date])})<=2

then 3

elseif

DATEDIFF('month',[Order Date],{ fixed : MAX([Order Date])})>=12

and

DATEDIFF('month',[Order Date],{ fixed : MAX([Order Date])})<15

then 3 else 0 END

Then you can doe this

• ###### 5. Re: Accumulate a Calculation Within ElseIF Statements

You can display all the bins with subtotal and totals, but it does't look pretty well. I think is better with the parameter. You can see the solutions in sheet 3 and 4.