11 Replies Latest reply on Jul 22, 2018 8:39 AM by Abhilash Sharma

# Rolling 12 month date bins

Hello -

I am trying to build dynamic rolling 12 month date bins to place data. In my data set, I have a launch date dimension that is in standard date form. I want to create 4 rolling 12 month bins:

1) All launch dates that fall within 12 months from today

2) All launch dates that fall between 12 months and 24 months from today

3) All launch dates that fall between 24 and 36 months from today

4) All launch dates that fall between 36 and 48 months from today

I am struggling with a calculated field to do this. Any pointers?

Thanks,

Kate

• ###### 1. Re: Rolling 12 month date bins

[Anyone working this one?]

--Shawn

Shin

• ###### 3. Re: Rolling 12 month date bins

At that level? Day or Month? Meaning 7/5/2015 to 7/6/2016? Or 6/30/2015 to 7/1/2016?

Essentially are we actually talking months or days within months?

--Shawn

• ###### 4. Re: Rolling 12 month date bins

Hi Kate

Shawn raises some useful questions... bucketing time periods is never normally straight forward.

But to give you a pointer - datediff()

So last 12 months from today is

IF DATEDIFF('month',[your date field],TODAY()) < 13 THEN 'SHOW' ELSE 'HIDE' END

Put this field on the filter shelf - set to SHOW.

Or if you actually want the last 365 days...

IF DATEDIFF('day',[your date field],TODAY()) < 366 THEN 'SHOW' ELSE 'HIDE' END

Put this field on the filter shelf - set to SHOW.

By playing around with the <, <=, > and >= you can get what you need.

You can also extend the formula with AND (if you want both to be true), or OR (if you want either to be true)

So, 12 - 24 months...

IF DATEDIFF('month',[your date field],TODAY()) >= 12 AND DATEDIFF('month',[your date field],TODAY()) < 25 THEN 'SHOW' ELSE 'HIDE' END

Put this field on the filter shelf - set to SHOW.

I'll stop there, this is Shawn's, but I hope that gives you a start!

If anything doesn't make sense, you can of course ask!

Cheers

Mark

• ###### 5. Re: Rolling 12 month date bins

Now it's your's Mark!

• ###### 6. Re: Rolling 12 month date bins

Only because of time difference... You would have said the same...

@katie, has everything above made sense? do you have any questions?

• ###### 7. Re: Rolling 12 month date bins

Hi -

Thanks for all of your help. I will test this out today and let you know how it goes. Really appreciate it!!

Kate

• ###### 8. Re: Rolling 12 month date bins

Hi Kate

I was reminded yesterday about something I wrote a while back, maybe helpful to you

Its using a reference date (so its not affected by filters) but the general technique could be tweaked for your needs.

In combination with the above,Ii hope you have all you need, if not, or you run you run into problems, let us know

Cheers

Mark

• ###### 9. Re: Rolling 12 month date bins

Hi @MARK FRASER

I have been trying to play around the same for a WEEKLY level bin such as 1week, 4week, 6week and 8week. But I dont seem to be able to have the right calculation.

Currently all I am using is this:

DATEPART('week', [Get Date])

So I get date values, but unfortunately they dont fall under correct buckets when I toggle between values.

Any help is welcome. @Shinichiro Murakami

• ###### 10. Re: Rolling 12 month date bins

Hi Abhilash

Thanks,

Shin

• ###### 11. Re: Rolling 12 month date bins

Sure Shin!

Here's the link: How to create Week based bins?