1 2 Previous Next 19 Replies Latest reply on Apr 24, 2018 12:23 PM by Lauren Brown

Rolling 12 months forwards and backwards - different order start dates

I have attached sample data in a workbook with accounts that all have sales of two item codes.

What I am trying to do is show rolling 12 month sales from the start date of the when the customer started buying "ITEM B." In addition, show rolling 12 month sales prior to the date the customer starting buying "ITEMB," of "ITEMA" sales.  The challenge here is that each customer has a different start date of when they starting buying "ITEMB."

Here is what I have tried and have not been successful:

Moving table calculation but it only works when I am filtered on one account number

Fixed calculation

Date Diff calculation

Calculation with a reference date

Any insight would be greatly appreciated!

• 1. Re: Rolling 12 months forwards and backwards - different order start dates

Hi

see the attached

it returns this

first determine "The Date"

then the forward and back are

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
• 2. Re: Rolling 12 months forwards and backwards - different order start dates

Hi Jim,

Thank you so much! I added in the calculations to my workbook and so far, the calculation to give me the sales forward is working.

I cannot seem to get the 12 month sales back showing anything in my view.

Let me explain my data a bit...I have all my items grouped into two codes.  One group is B code and the other is Non B code.

This calculation is working to show sales forward of when the customer starting buying B Code items.

My next step it to show sales of the Non B Group 12 months prior to buying the B Code.

Here is the calculation I put in but it is not displaying anything.

• 3. Re: Rolling 12 months forwards and backwards - different order start dates

now to your immediate issue - you did not include the formula for the fixed min date - that could create an issue if you look at my solution ALL the dates are based on ITEMB (i.e. even the dates for ITEMA)

the other area things can go wrong is in the conditional statement for "None B Code" which is in the formula but you reference "Non B Code" in your post - the check is literal and the 2 won't match

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: Rolling 12 months forwards and backwards - different order start dates

Jim, I figured it out now. I had the group calculation in my date calculation, once I took that out the 12 months back worked!

Thanks again for your help with this!

• 5. Re: Rolling 12 months forwards and backwards - different order start dates

Thanks Lauren

I was glad to help out -

Jim

• 6. Re: Rolling 12 months forwards and backwards - different order start dates

I did? Sorry I am new to this.

Lauren Brown

• 7. Re: Rolling 12 months forwards and backwards - different order start dates

Understood

Thanks

Jim

• 8. Re: Rolling 12 months forwards and backwards - different order start dates

I think I fixed it and I did mark it correct.

Thanks again

Lauren Brown

Sr Analyst, Sales Op Management - Sustainable Technologies

15 Hampshire Street Mansfield, MA 02048

508.261.8148 dir | 844.COVALUE field service

• 9. Re: Rolling 12 months forwards and backwards - different order start dates

I also need help in an additional calculation.

For the calculation "Item B 12 Forward" - I have some accounts that have just started purchasing this year and do not have a full 12 months of sales data to show.  I need a calculation that would show those accounts predicted sales for the additional months needed to make up the 12.

Is that possible?  My thoughts are that it would be some type of IF calculation however, I don't know where to begin and I am not familiar with predictive calculations

• 10. Re: Rolling 12 months forwards and backwards - different order start dates

Lauren I would have to see how you would propose the "predicted sales"

there is a formula I really like for this                 ifnull(expression1,expression 2)

the way it works is if expression 1 is NOT null then that is the value used - if it is null then expression 2 is used

that said it would have to be worked into your 12 month forward looking formula

JIm

• 11. Re: Rolling 12 months forwards and backwards - different order start dates

Jim,

What exactly would you need to see? That way I can add it into the workbook and repost.

That sounds like exactly what I need! And I was going to ask next if it could be added into the existing formula, which is great that it can!

• 12. Re: Rolling 12 months forwards and backwards - different order start dates

I would just need to understand how you get the how you get the predicted sales - is that another dimension

the current formula is something like this (I know it is not exact )

{ INCLUDE [Account]: if min(Item)="ITEMB" then

sum( if datetrunc('month',[Date])>=datetrunc('month',[lod account product min date])

[lod account product min date])) then [Sales] end ) end }

but essentially what this does in words is "check the date and if it is in the period and add sales "  I'm thinking the last part of this becomes

....... then      ifnull([Sales],[Predicted sales])   end ....

but that assumes that predicted sales is a dimension in your data set and has dates associated with it

you could give it a try and see what happens - if you do and it blows up that is a step in the right direction

let me know

Jim

• 13. Re: Rolling 12 months forwards and backwards - different order start dates

Jim,

Ok, I understand.  It is not another dimension.  Does it have to be?

Lauren Brown

Sr Analyst, Sales Op Management - Sustainable Technologies

• 14. Re: Rolling 12 months forwards and backwards - different order start dates

No it doesn't have to be a dimension but I would need to understand what it is  and how it is determined -

the reason is when we get to picking up previous values or looking across several previous months to get a trend we leave simple calculation and move into table calculations and it can be tricky

Jim

1 2 Previous Next